cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Implement Row Total for Table With Parameter as columns

I had a table that i pivoted on name that summarizes revenue. I added all the name columns to a parameter. Then, I put the parameter into the table so each name is listed as you can see below:

;

I then wrote a measure called "Revenue Sum" and added it as the last column of the table. The intention here is that it will sum every column that we added to the parmeter:

Revenue Sum = SUMX('Sheet1 (3)','Sheet1 (3)'[Anna]+'Sheet1 (3)'[Bob]+'Sheet1 (3)'[Brooke]+'Sheet1 (3)'[Brent]+'Sheet1 (3)'[Bruce]+'Sheet1 (3)'[Cam]+'Sheet1 (3)'[Cami]+'Sheet1 (3)'[Dan]+'Sheet1 (3)'[Emma]+'Sheet1 (3)'[Eva]+'Sheet1 (3)'[Frank]+'Sheet1 (3)'[Garret]+'Sheet1 (3)'[Joe]+'Sheet1 (3)'[Kyle]+'Sheet1 (3)'[Meghan]+'Sheet1 (3)'[Philip]+'Sheet1 (3)'[Rachel]+'Sheet1 (3)'[Steve]+'Sheet1 (3)'[Ted]+'Sheet1 (3)'[Tom])

Lastly, when I filter on the parameter columns, the Revenue Sum measure doesn't adjust. As you can see, the totals remain the same for when all the parameter values are included:

How can I write a measure/dax expression that included a total value for the all the parameterized columns that adjusts for when they are filtered out of the visualization.

Also, I can't use a matrix visual for this particular task although I know that would make things a lot easier

1 ACCEPTED SOLUTION
Frequent Visitor
For those curious, I believe I found a solution that is so far working. First, I joined the base table (before pivot) to the parameter table on name in the model via manage relationships. Then, I wrote a measure that sums revenue in the base table and filters on all the columns I have in my finished visualization: sum 2 = CALCULATE(SUM(Sheet1[Revenue]), FILTER(Sheet1, Sheet1[Country]=SELECTEDVALUE('Sheet1 (3)'[Country]) && Sheet1[Project Name] = SELECTEDVALUE('Sheet1 (3)'[Project Name]) && Sheet1[Rev>Cost] = SELECTEDVALUE('Sheet1 (3)'[Rev>Cost]) && Sheet1[Column5] = SELECTEDVALUE('Sheet1 (3)'[Column5])))
This appears to be working for me

2 REPLIES 2
Frequent Visitor
For those curious, I believe I found a solution that is so far working. First, I joined the base table (before pivot) to the parameter table on name in the model via manage relationships. Then, I wrote a measure that sums revenue in the base table and filters on all the columns I have in my finished visualization: sum 2 = CALCULATE(SUM(Sheet1[Revenue]), FILTER(Sheet1, Sheet1[Country]=SELECTEDVALUE('Sheet1 (3)'[Country]) && Sheet1[Project Name] = SELECTEDVALUE('Sheet1 (3)'[Project Name]) && Sheet1[Rev>Cost] = SELECTEDVALUE('Sheet1 (3)'[Rev>Cost]) && Sheet1[Column5] = SELECTEDVALUE('Sheet1 (3)'[Column5])))
This appears to be working for me

Frequent Visitor

https://community.powerbi.com/t5/Desktop/Passing-Parameters-in-measures/m-p/208276 only workaround ive found so far but it still hasnt led to a solution

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.