CODEWITHSUNDEEP
powerbidaxpowerbi-embedded
Serdia
Serdia

Reputation: 4428

How to divide by value based on a date, not total value in Power BI

The total Count for Status = "Submitted" is 2,320.

I need to calculate column Ratio, so column Count for each Status need to be divided by Count where Status = "Submitted" (1,655).

But instead it divides by Total Count of Status = "Submitted" (2,320) The expression for calculated column Ratio looks like this:

Ratio = DIVIDE(Number_3[Count],CALCULATE(SUM(Number_3[Count]),FILTER(Number_3,Number_3[Status] = "Submitted")))

enter image description here

The result should look like this:

enter image description here

Relationship in the model look like this:

enter image description here

UPDATE:

If I select fill date range then Count of Submitted will be 2,320

enter image description here

Update: .ipbx file is available here: https://www.dropbox.com/s/dp063gf48sbvexc/TestPredictions.pbix?dl=0 . Its on Page 2. Query 'Number_3'

Upvotes: 0

Views: 3697

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

The numerator was indeed funny looking! Your Ratio should be a measure, not a calculated column. (Calculated columns are never affected by any slicers or filtering on your report pages.)

Delete your Ratio calculated column and replace it with the following measure:

Ratio = DIVIDE(SUM(Number_3[Count]),
               CALCULATE(SUM(Number_3[Count]),
                         ALL(Number_3[Status]),
                         Number_3[Status] = "Submitted"))

Notice how I have a SUM aggregation in the numerator instead of a naked column name. The ALL function is there so statuses other than Submitted return a percentage as well.

Upvotes: 1

Related Questions