topic Re: Measure Totals in a table visual using Field Parameters in DAX Commands and Tips

Measure Totals in a table visual using Field Parameters

marcuspaula — Fri, 21 Jul 2023 23:05:48 GMT

Hello, community!

Is there a way to dynamically calculate totals in a table using Field Parameters?

I have the following data:

The following Field Parameter:

Parameter - Dimensions = { ("Category1", NAMEOF('Table'[Category1]), 0), ("Category2", NAMEOF('Table'[Category2]), 1) }

And the following measures:

.Sum = SUM('Table'[Value])-50

.Sum Field Parameter = VAR __table = SUMMARIZE( 'Parameter - Dimensions', 'Parameter - Dimensions'[Parameter - Dimensions Fields], "@row",[.Sum] ) RETURN IF( HASONEVALUE('Parameter - Dimensions'[Parameter - Dimensions Fields]), [.Sum], SUMX(__table,[@row]) )

And this is the result (3500) I was expecting for my second measure on a Matrix visual:

And the result I would expect if selecting Category1 (3450):

PS. if I use the following

.Sum Field Parameter := SUMX( VALUES('Table'[Category1]), [.Sum] )

I have the desired result for the total row (which is the sum of rows). But I would like to dynamically change the column in the SUMX to whatever is selected on the field parameter if possible.

Greg_Deckler

Re: Measure Totals in a table visual using Field Parameters

Anonymous — Mon, 24 Jul 2023 01:46:50 GMT

HI marcuspaula,

I'd like to suggest you add switch function to your measure formula to interact with field parameter filter effects and redirect to different calculations:

How to dynamically switch between measures in Power BI visuals with Field Parameters | endjin - Azure Data Analytics Consultancy UK

Regards,

Xiaoxin Sheng

Re: Measure Totals in a table visual using Field Parameters

marcuspaula — Wed, 26 Jul 2023 16:26:03 GMT

Hi Anonymous,

I was trying to avoid the SWITCH since I have like 30 different scenarios but it looks like this is the way to get this working.

Thank you.