How to calculate the percentage of values group by date in power bi

1
votes

I am completely new to power bi. I have the sample data table as below.

Data Sample[1]

I want to calculate the percentage of PASS percentage(Total Number of Pass cases/Total Number of Cases) and FAIL percentage (Total Number of Fail Cases/Total Number of Cases) status for each day. I tried as below but it's not giving me the expected results.

Blocked by date = Calculate (
                     countrows (TableName),
                    (TableName[TestDate].[Date],[Total BLOCKED])
)

Here Total BLOCKED is the measure I created to filter the status.

Where am I going wrong here? How should I calculate the percentage of PASS and FAIL status for each day?

2
reporting-servicespowerbidata-visualizationdax

2 Answers

1
votes

It would be similar to this:

The total number of statuses measure:

status_total = 
    VAR passed = COUNT(Table_1[status])
RETURN IF( ISBLANK(passed), 0, passed)

Passed measure:

passed = 
    VAR passed = CALCULATE(
                    COUNTROWS(Table_1),
                    FILTER(Table_1, Table_1[Status] = "PASS")
    )
RETURN IF( ISBLANK(passed), 0, passed)

Failed measure:

failed = 
    VAR failed = CALCULATE(
                     COUNTROWS(Table_1),
                     FILTER(Table_1, Table_1[Status] = "FAIL")
    )
RETURN IF( ISBLANK(failed), 0, failed )

PASS ratio:

passed % = DIVIDE([passed], [status_total],0)

FAIL ratio:

failed % = DIVIDE([failed], [status_total],0)

Result:

enter image description here

Those three separate measure, of course, can be combined into one if needed

0
votes

you can also use quick measure "divide" and that would be filter based...count of that col "status" with filter "pass", divided by count of col "status" without any filter....