powerbidaxpowerbi-desktop

Add extra column for percent

I loaded a table which has columns name of issue, date of issue and weeks_in_work for each issue.

I have visualized table with weeks_in_work and amount of issues

Something like this

| weeks_in_work| Amount issues | | ----- | ------| | 1| 21| | 2| 10| | 3| 5|

I want to add another column

ratio of the number of issues for each week to the total number with accumulation | weeks_in_work | Amount issues | percent| |---- |------| -----| | 1| 21| 58%| | 2| 10| 86%| | 3| 5| 100%|

I created a measure

AmountOfIssuesPerWeek = 
CALCULATE(
    COUNTA('Query'[issue_name]),
    ALLEXCEPT('Query', 'Query'[weeks_in_work])
)

And

AllIssues = COUNT('Query'[issue_name])

Then

Percent = 
DIVIDE([AmountOfIssuesPerWeek ], [AllIssues]) * 100

As you can see I got strange numbers. The problem is I have a filter on date, so I can't create a table Is is it possible to make this using measures and with working filter? Help me please :)

UPD: Attached the model and screen how it looks like in PBI

enter image description here

https://drive.google.com/file/d/1epF19AY6XVc6wFlS-3hoFyB296rYxQTJ/view?usp=drivesdk

Solution

  • I can't open the link.

    So I created the similar data. I used sum, you can try to change sum to count

    count of s.issue_name = sum('Table'[Issue_name])

PROPORTION =
[count of s.issue_name]
    / CALCULATE ( SUM ( 'Table'[Issue_name] ), ALL ( 'Table' ) )

P2 =
VAR _PROPORTION = [PROPORTION]
RETURN
    CALCULATE (
        [PROPORTION],
        FILTER (
            ALL ( 'Table'[weeks_in_work] ),
            'Table'[weeks_in_work] <= MAX ( 'Table'[weeks_in_work] )
        )
    )

    enter image description here