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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Table Visual - Grand Total Not Matching Row Amounts Total

Hello,

 

I'm struggling to find the right DAX formula so my Grand Total in the right-most column in my Table visual is correct when compared to the total amount of the row level data. I think what is happening is that my DAX formula for Total Service Penalties Based on Qty Fill Rate % sees the 96.5% and then takes the total amount from the Service Penalty at 1.5% measure. This is incorrect when looking at the individual row values for the Total Service Penalties Based on Qty Fill Rate % column. The total amount I want to see for the Total Service Penalties Based on Qty Fill Rate % column is $ 360,084.89 which would be a summation of the individual row amounts. How would I achieve this result? I've tried leveraging variables and the HASONEVALUE() approach but my total ending up being inflated to approx. $1.01 million even though the individual row amounts were correct.

 

TableVisual-IncorrectGrandTotalvsRowAmountsTotal.PNG

 
Here is the DAX formula for the last column on the right-sde of the table visual....
Total Service Penalties Based on Qty Fill Rate % = IF('HANA Orders - Order Level'[Qty Fill Rate %]>=.90,'HANA Orders - Order Level'[Service Penalty at 1.5%],IF('HANA Orders - Order Level'[Qty Fill Rate %]>=.80 && 'HANA Orders - Order Level'[Qty Fill Rate %]<.90,'HANA Orders - Order Level'[Service Penalty at 2.0%],'HANA Orders - Order Level'[Service Penalty at 5.0%]))
 
DAX formulas for the measures being used in the measure Total Service Penalties Based on Qty Fill Rate %....
Qty Fill Rate % = SUMX('HANA Orders - Order Level','HANA Orders - Order Level'[Line Ship Qty]) / SUMX('HANA Orders - Order Level','HANA Orders - Order Level'[Line Order Qty])
 
Service Penalty at 1.5% = CALCULATE(SUMX('HANA Orders - Order Level','HANA Orders - Order Level'[Invoice Gross Base Price])*.015,'HANA Orders - Order Level'[Service Penalty Applicable?]="Yes")
 
Service Penalty at 2.0% = CALCULATE(SUMX('HANA Orders - Order Level','HANA Orders - Order Level'[Invoice Gross Base Price])*.02,'HANA Orders - Order Level'[Service Penalty Applicable?]="Yes")
 
Service Penalty at 5.0% = CALCULATE(SUMX('HANA Orders - Order Level','HANA Orders - Order Level'[Invoice Gross Base Price])*.05,'HANA Orders - Order Level'[Service Penalty Applicable?]="Yes")
 
 
1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

Please try using this DAX pattern referencing your existing measures to get the correct numbers on both the rows and the total.

 

NewMeasure = SUMX(VALUES(Table[DC Name]), [YourExistingMeasure])

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Employee
Employee

Please try using this DAX pattern referencing your existing measures to get the correct numbers on both the rows and the total.

 

NewMeasure = SUMX(VALUES(Table[DC Name]), [YourExistingMeasure])

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat  Can you please elaborate on what [DC Name] in your response represents?

Anonymous
Not applicable

@bchager6 , @mahoneypat 

 

DC Name is the name of the Customer's Distribution Center name that orders are shipped to after they've been fulfilled. Leveraging the VALUES() DAX function with [DC Name] tells which level of granularity to perform the calculation at.

 

Hope this helps.

Anonymous
Not applicable

Giddy up...this worked. Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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