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.
I created the below table visual to display total charges. The current view is drilled down one layer to the day of the month. The problem is that I need the total to equal $164,082, not $382,335. The reason the total is this high is that it is duplicating the charges for the same callkey. e.g. Call 716533 was only charged $54,171 total, but because this is broken down by day it is tripling this amount.
These are the DAX formulas I'm using to calculate the above columns.
0mo charges3 = SUMX(SUMMARIZE(Intra_Month_Chart1_Test,Intra_Month_Chart1_Test[DaysElapsed_Grouped],Intra_Month_Chart1_Test[net_payment_amount],"New Charges",MAX([0mo charges])),[New Charges]) 0mo charges4 = SUMX(SUMMARIZE(ALL(Intra_Month_Chart1_Test[DaysElapsed_Grouped],Intra_Month_Chart1_Test[net_payment_amount],Intra_Month_Chart1_Test[callkey]),Intra_Month_Chart1_Test[callkey],"Test",MAX([0mo charges])),[Test])
So what I want is just one column containing the charges, but to display the correct charges per call without these charges being duplicated in the final total row.
Solved! Go to Solution.
Hi @ironryan77,
Based on your description, you want to display the total value of '0mo charges4' measure on '0mo charges3' total row, right?
If this is a case, you can add some condition to filter the total row and use the specified formula to instead the original calculation.
Conditional Measure= IF(COUNTROW(Intra_Month_Chart1_Test)=COUNTROW(ALL(Intra_Month_Chart1_Test)), SUMX(SUMMARIZE(ALL(Intra_Month_Chart1_Test[DaysElapsed_Grouped],Intra_Month_Chart1_Test[net_payment_amount],Intra_Month_Chart1_Test[callkey]),Intra_Month_Chart1_Test[callkey],"Test",MAX([0mo charges])),[Test]),//charges4 SUMX(SUMMARIZE(Intra_Month_Chart1_Test,Intra_Month_Chart1_Test[DaysElapsed_Grouped],Intra_Month_Chart1_Test[net_payment_amount],"New Charges",MAX([0mo charges])),[New Charges]))//charges3
Sample:
Modify TotalRow = IF(COUNTROWS(Sheet2)=COUNTROWS(ALL(Sheet2)), "Total row formula", SUM(Sheet2[Amount])) //original formula.
Regards,
Xiaoxin Sheng
Hi @ironryan77,
Based on your description, you want to display the total value of '0mo charges4' measure on '0mo charges3' total row, right?
If this is a case, you can add some condition to filter the total row and use the specified formula to instead the original calculation.
Conditional Measure= IF(COUNTROW(Intra_Month_Chart1_Test)=COUNTROW(ALL(Intra_Month_Chart1_Test)), SUMX(SUMMARIZE(ALL(Intra_Month_Chart1_Test[DaysElapsed_Grouped],Intra_Month_Chart1_Test[net_payment_amount],Intra_Month_Chart1_Test[callkey]),Intra_Month_Chart1_Test[callkey],"Test",MAX([0mo charges])),[Test]),//charges4 SUMX(SUMMARIZE(Intra_Month_Chart1_Test,Intra_Month_Chart1_Test[DaysElapsed_Grouped],Intra_Month_Chart1_Test[net_payment_amount],"New Charges",MAX([0mo charges])),[New Charges]))//charges3
Sample:
Modify TotalRow = IF(COUNTROWS(Sheet2)=COUNTROWS(ALL(Sheet2)), "Total row formula", SUM(Sheet2[Amount])) //original formula.
Regards,
Xiaoxin Sheng
Awesome! This works for me with one noteable addition. I changed the first DAX line of code from
COUNTROW(ALL(Intra_Month_Chart1_Test)),
to
COUNTROW(ALLSELECTED(Intra_Month_Chart1_Test)),
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |