Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago
    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago
    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