Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

incorrect column subtotal in my Power BI Matrix visual

I am trying to get the correct column subtotal for a particular field which is being calculated by the following measures 

1. #SavingsTSMGlobal = 

CALCULATE(SUM(Savings[sum(Risk Percentage*USLP Savings)])/1000000,FILTER(Savings,Savings[ICB]="YES"),Savings[Scenario]="YTD",Savings[Strategic Pillar]="TSM initiative")
 
2.#SavingsTSMLocal = 
CALCULATE(SUM(Savings[sum(Risk Percentage*USLP Savings)])/1000000,FILTER(Savings,Savings[ICB]="NO"),Savings[Scenario]="YTD",Savings[Strategic Pillar]="TSM initiative")
 
3. 
#SavingsTotal = 
IF(
CONTAINS(Savings,Savings[Location - Region (L1)],"Global"),[#SavingsTSMGlobal],[#SavingsTSMLocal])
 
 I am putting my #SavingsTotal in my matrix visual but when I turn on the column subtotals, all the other fields is coming correct but for this the column subtotal is taking #SavingsTSMGlobal value instead of actually calculating the total. 
I am struggling since 6 hours to get this right. Nothing seems to work. I switched to Table Visual but still the TOTAL value came wrong. 
A little background on my matrix visual 
Column sections includes CLUSTER column name coming from CLUSTER MASTER file which is connected to my SAVINGS file using Many to Many (Cluster master filters region 1 ) active relatioship. 
I have also turned on the "show on rows". 
There are no rows present. 

Any help is appreciated!!
 
2 REPLIES 2
Anonymous
Not applicable

@amitchandak Hey, I think the problem lies with #SavingsTotal statement. The column subtotal is only taking the value present in the TRUE part of the IF Statement. 
I wont be able to share the file , I will try arranging for screenshots. 

amitchandak
Super User
Super User

@Anonymous , formula see fine. You can try like

CALCULATE(SUM(Savings[sum(Risk Percentage*USLP Savings)])/1000000,FILTER(Savings,Savings[ICB]="NO" && Savings[Scenario]="YTD" && Savings[Strategic Pillar]="TSM initiative"))

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.