Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
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:
Regards,
Xiaoxin Sheng
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:
Regards,
Xiaoxin Sheng
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.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |