Power BI count instances of records that match criteria based on What-If parameter

Question

I have an issue trying to count rows in a Power BI table where the criteria varies based on a What-If parameter. The table is about 11 million rows long but I'll try to give a simple example of what I'm trying to do. Forgive the length of the post but there's actually not much too it.

The data as it comes in from SQL Server looks something like this (simplified for clarity)

Item    Category    Price    AvgCatPrice    Variance
Dog     Pet         20.00    15.00          33.33
Cat     Pet         12.00    15.00          20.00
Goat    Pet         18.00    15.00          20.00
Maggot  Pet         05.00    15.00          66.67
Apple   Fruit       01.00    01.20          16.67
Orange  Fruit       01.20    01.20          00.00
Mango   Fruit       01.30    01.20          08.33

The variance is simply the absolute difference between price and average for the category as a percentage but that's not relevant to the question

So I load the data into Power Bi and everything looks like the above.

I then add a simple calculated column as Count =1.

If I put these values on a table with Category as rows and Count as Values I get

Category  Count
Fruit       3
Pet         4

This is as expected.

I then added a what-if parameter called VarPc as a decimal number between 0 and 100 with increments of 5. I took the option to automatically add a slicer for the parameter.

I then add a new column like this.

OverVarPc = IF(SOTestDat[Variance] > VarPc[VarPc Value],1,0)

If I add this to the table I get

Category  Count  OverVarPc
Fruit       3      2
Pet         4      4

..which is correct assuming that VarPc is zero.

Now the first issue If I adjust the parameter via the slider or input box, the OverVarPc column is not reevaluated.

Next approach I added a second table, this time all detail is shown (no deliberate aggregation). I then added a new measure to the VarPc table called SumIfGt with this as the definition

SumIFGt = IF((sum(SOTestDat[Variance]) - sum(VarPc[VarPc])) >0,1,0)

The table looks like this with the slider at 0%

Item    Price   Variance    Count   OverVarPc   SumIFGt
Apple   1       16.67       1       1           1
Cat     12      20          1       1           1
Dog     20      33.33       1       1           1
Goat    18      20          1       1           1
Maggot  5       66.67       1       1           1
Mango   1.3     8.33        1       1           1
Orange  1.2     0           1       0           0

If I set it to 25% then the table now looks like this.

Item    Price   Variance    Count   OverVarPc   SumIFGt
Apple   1       16.67       1       1           0
Cat     12      20          1       1           0
Dog     20      33.33       1       1           1
Goat    18      20          1       1           0
Maggot  5       66.67       1       1           1
Mango   1.3     8.33        1       1           0
Orange  1.2     0           1       0           0

Both results are as expected.

So here's me thinking I'm good to go.... Next Issue I drop this new measure onto my original aggregated table but the table now shows.

Category  Count  SumIfGt
Fruit       3     0
Pet         4     1

I sort of understand why it does this, it's aggregating SUM of Variance and (for fruit) getting 75 and then aggregating VarPc for each row (3 rows x 25) = 75 and then returning 0 as it's not greater than.

Finally the question! How can I get a simple count of the results of the measure. What I want to see in the final table is the Category, the total items in the category and the number of items whose variance is greater that the What-IF parameter.

There are a bunch more things I want to do but I think if I get this cracked the rest will follow easily.

Thanks for reading this far!

Answer 1

I think you might find things easier in the long run if, instead of adding a calculated column to 'SOTestDat': Count = 1 you instead add a measure to that table:

Count = COUNTROWS(SOTestData)

Having done this, your OverVarPc will evaluate correctly if you add it as a measure something like this:

OverVarPc = CALCULATE([Count], FILTER(SOTestDat, SOTestDat[Variance] > VarPc[VarPc Value]))

That will count the rows in the SOTestDat table after filtering for rows where the [Variance] is greater than the currently value of the [VarPc Value] measure.