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. 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")

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

Anonymous

Not applicable

6 years ago

Solved

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.

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")

Need Help

mahoneypat
6 years ago
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

Forum Discussion

Table Visual - Grand Total Not Matching Row Amounts Total

4 Replies

Related content

Table Visual not Matching Calculated Visual

Power Query Add rows of zero amount

Calculate bonus amount for retroactive months.

Amounts don't match up

Circle Visual Total amount