Group By Division, IF Statement & Proper Subtotal Calculation

[Helios]

I need help determining the logic issue with my ‘test’ and '‘What_if_70%’ measure, in particular, how the subtotal is aggregating. The subtotal is not showing the correct output but the row levels are.

In the ‘base data’ table (excel), I have randomized data showing function performance by location. The ideal output is below. The table in PBI should show a group by function and aggregate at subtotal correctly. At row level context, the DAX does what it’s supposed to do, and technically, at subtotal level it does what it should too, but the result is not what I want to see.

What the PBIX matrix looks like:

OUTPUT_%, Numer, and Denom all show the correct row level and subtotal result. But the measure ‘What_if_70_%’ is not showing correct subtotal. It should return [257 + 1,245] = [1,502]. Instead I see [1,305].

Conceptually, to fix the subtotals, I think the idea is to make a virtual table that performs a group by on function, and then to iterate a SUMX over it. I am trying to do the division of (group by sum of [Numer] / group by sum of [Denom]). Then I want to compare if that division result is greater than or equal 70% or 0.70. If it is >= 70%, then use [Numer], if it isn’t, then apply the 70% scenario = [Denom * 0.70]. This works in vacuum and very specific filters built into the logic itself, but fails when I try to interact with slicers on the visualizations page or insert into the matrix.

I spent too many hours on this and I’m at a loss. I feel my failure in asking but I don’t know why I don’t know or understand the answer here. I think the solution is fully around ADDCOLUMNS() and SUMMARIZE() but I am missing the context or not looking at the issue correctly.

OUTPUT_%_DAX = 
var Numer = SUM(Performance[Numer])
var Denom = SUM(Performance[Denom])

return 
DIVIDE(Numer,Denom)

What_if_70% = 
var x = SUM(Performance[Denom]) *0.70
var z = IF([OUTPUT_%_DAX] >= 0.70, SUM(Performance[Numer]),x)
return 
z

test = 

var sumTable = 
    CALCULATETABLE(
        ADDCOLUMNS(
            SUMMARIZE(Performance,Performance[Function]),
            "NUMER", SUM(Performance[Numer]),

            "DENOM", SUM(Performance[Denom]),

            "OUTPUT_%", DIVIDE(SUM(Performance[Numer]),SUM(Performance[Denom])),

            "What_if_70%", IF([OUTPUT_%_DAX] >= 0.70, SUM(Performance[Numer]), SUM(Performance[Denom]) *0.70)
            ),
        ALLSELECTED(Performance[Location]),
        ALLSELECTED(Performance[Year]),
        ALLSELECTED(Performance[Month]),
        ALLSELECTED(Performance[Function])
    )
        return 
        SUMX(sumTable,[What_if_70%])
   

Performance_Tbl_Example.pbix (55.6 KB)

[BINavPete]

Hi @Helios

Check this video by @BrianJ out. This helped me get through this issue.
How To Fix Matrix Totals In Power BI - YouTube

I’m about to stop for Easter, but if you get into difficulty, let me know and I’ll give it a go for you.

Pete

[Harsh]

Hello @Helios,

Thank You for posting your query onto the Forum.

Our expert @Greg had already created a post on “How to Fix Incorrect Totals”. Below is the link of that post provided for the reference.

And you can fix your “What_if_70%” totals by writing the measure as provided below -

What_if_70% - Totals = 
SUMX(
    SUMMARIZE(
        Performance , 
        Performance[Function] , 
        "@Totals" , 
        [What_if_70%] ) , 
    [@Totals]
)

Now, to fix the totals for “Delta” reference the above provided measure inside the measure which has been created for Delta. And then you’ll see the correct results at a total level. Below is the screenshot of the final results provided for the reference -

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Performance_Tbl_Example - Harsh.pbix (59.8 KB)

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

[Helios]

@Harsh

Thank you for the solution.

A question about a formula quirk if you can-. Why does referencing ‘What_if_70%’ as an individual measure return the right answer?

I had a few solutions (test_v2 formula) where I did the same exact logic inside the overall formula. However, ‘test_v2’ returned a completely different total aggregate than ‘test’. Nothing fundamentally changed but calling the same logic inside the same measure produced a different result versus calling upon the measure externally.

test = 

var final = SUMX(
    SUMMARIZE(
        Performance , 
        Performance[Function] , 
        "@Totals" , 
        [What_if_70%] ) , 
    [@Totals]
)


return 
final 

test _v2 = 

var x = SUM(Performance[Denom]) * 0.70
var logic = IF([OUTPUT_%_DAX] >= 0.70, SUM(Performance[Numer]),x)

var final = SUMX(
    SUMMARIZE(
        Performance , 
        Performance[Function] , 
        "@Totals" , 
        logic ) , 
    [@Totals]
)


return 
final

[Harsh]

Hello @Helios,

The reason why second formula is giving inflated results is because it is first considering the sum of each and every individual row level as per “VAR X” i.e., “COLD CALL = 256.90” and “EMAIL = 1,047.90” and then when you added the another condition i.e., “VAR FINAL”, it just summed up the results by 2x.

So since previously you had the wrong total as “1,304.80”, now because of that additional variable which you added inside the same formula it inflated the results.

It doesn’t makes sense to add that additional condition inside the same measure where you’re already getting the wrong results at a total level.

If it would have given me the right results by following that technique, then I would have performed that action in the first instance itself rather than creating additional measure for fixing the totals.

If you want to furthermore understand, why you’re getting wrong totals at the first instance then I’ve already provided my solutions onto the forum explaining the reason behind that and those scenario’s are also applicable in your case as well. Below are the links of those posts provided for the references.

Unless and until one has developed very strong understanding of the virtual calculations, it’s better to solve this type of problems by creating additional small measures also known as “Measure Branching Technique” like it has been showcased in the post “How to Fix Incorrect Totals” by our expert. Because when the calculations happen virtually, the error tracing becomes difficult like you faced in this scenario.

Thanks and Warm Regards,
Harsh

Wrong values on total

Hello @Harris, Thank You for posting your query onto the Forum. Well to fix the totals you’ll just be required to write a small additional formula. One of our expert @Greg has already created a post onto the forum which specifically addresses this type of issue. Below is the link of that post provided for the reference purpose. But when you write the formula you’ll see that how the context at the back-end is getting evaluated. On the visual you’re getting the results as “2.38%”. But at the ba…

Exclude Zero results in Overall Calculation?

Hello @gkavesh, Thank You for posting your query onto the Forum. Well just couple of days back I exactly addressed this same kind of issue onto the forum. Below is the link of that post provided for the reference. Please feel free to check out that post. Here’s your solution provieded below - (Let’s analyse this step-by-step) 1). The formula which you had used for “Productivity Rate” is as follows - Productivity Rate = DIVIDE( [Arrived - 1] , [Clinic Hours] , 0 ) Now, since you’ve complet…