I have an issue trying to count rows in a Power BI table, where the criteria vary 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 than 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!