Measure to Sum specific rows and the subtotal/grand total to still display the Actual Total Sales

BILearner — Tue, 20 Oct 2020 21:54:24 GMT

Hi Power BI Community,

I have a table displayed like below, with column definitions as follows.

Tot Sales = Sum(Sales)
My Formula = Is my attempt to get the expected result.
Expected Result = How I want the calculation to work.

Here is the DAX I wrote

SUMX(SUMMARIZE(SalesFact,'Territory'[Territory ID],'Product'[Sales Role]),IF('Product'[Sales Role]="Cat 1 Rep",CALCULATE([Total Sales],'Product'[Cat 1 Product]="YES",ALL('Product')),IF('Product'[Sales Role]="Cat 2 Rep",CALCULATE([Total Sales],'Product'[Cat 2 Rep Product]="YES"),CALCULATE([Total Sales],'Product'[Cat 3 Rep Product]="YES"))))

The above formula gives me the expected result against the Cat 1 Rep row, but fails to get the expected sub total and grand total.

The field "Cat 1 Rep Product" in above formula is a flag which identifies products from product table that fall into Cat 1.
"Cat 2 Rep Product" and "Cat 3 Rep Product" are similar columns.

What I want is - Sum the results for Cat 1 Rep row and Cat 2 Rep row and display it against the Cat 1 rep, other rows shouldn't be impacted. Also, I do not want this manipulation to impact the Column subtotal or grand total and should display the actual total that is (Cat 1 Rep Product + Cat 2 Rep Product + Cat 3 Rep Product) and
NOT ( (Cat 1 Rep Product + Cat 2 Rep Product) + Cat 2 Rep Product + Cat 3 Rep Product).

Is this possible?

Thank you all!

Re: Measure to Sum specific rows and the subtotal/grand total to still display the Actual Total Sales

amitchandak — Wed, 21 Oct 2020 01:33:54 GMT

BILearner , you have to use isinscope and isfiltered to get correct subtotal and grand totals

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

topic Re: Measure to Sum specific rows and the subtotal/grand total to still display the Actual Total Sales in DAX Commands and Tips

Measure to Sum specific rows and the subtotal/grand total to still display the Actual Total Sales

Re: Measure to Sum specific rows and the subtotal/grand total to still display the Actual Total Sales