Aggregate Discrepancies Between Visualization Choices

[Anonymous]

I'm stumped on an aggregate value difference between two visualizations that use the same custom measure to feed the graph. Essentially, I'm calculating turnover in a group and the basic measure is # of terms/active total. An image is attached below for a reference if that helps.

a.) When using the waterfall chart, it sums the monthly total to give me an aggregate - In this case 10.11%

b.) When using the card or matrix version, BI is essentially taking the total terms/active total in the last of the month - In this case 10.27%

I understand that neither calculation is necessarily wrong, but for consistency, I would want them to always be summed like "A" instead of "B". Does anyone know how to easily do this within BI so the sum value doesn't change by visualization chosen? My only thought was to go the route of a complicated DAX SWITCH query, but I'd really prefer to go a simpler route if possible.

Thanks,

DaveOne version gives me 10.27 and the other is 10.11

[MFelix · Accepted solution]

Hi @Anonymous,

To get the a. option and looking at the way you show the information on your image you need to aggregate your measure in a SUMX something like:

SUMX(allselected(table[fiscalmonth]) ; [measure])

This will calculate your measure month by month, store the value and the sum all those rows in the end.

This may have to be adjusted since you don't provide any data example for usage.

Regards,
MFelix

[Anonymous]

@MFelix - You're a genius! That was exactly what I needed to solve this issue. Thank you so much!