Forum Discussion
Add parameter value to a specific table/matrix column value to simulate totals
Hello all,
I'm looking for a way to change the value of a specific column in a matrix to simulate totals based on a value of a parameter. I need to change the "Role A" value in column "31-Dec-99".
I wonder if a possible solution by either:
a) Replace the value directly based on the entry of the parameter value SIM A
b) create a separate table with the parameter value SIM A and then append/combining to the matrix alongside the original values and get the sum of it
c) create a 3rd set of values based on totals from the table and then add the parameter value
d) Another simple solution, since I´m a newbie, messing with complex formulas is challenging!
The original table looks like this:
| Role | Sum of Value | Date |
| A | 10 | 02-Jan-23 |
| A | 10 | 03-Mar-23 |
| A | 0 | 31-Dec-99 |
| B | 20 | 02-Jan-23 |
| B | 20 | 03-Mar-23 |
| B | 0 | 31-Dec-99 |
| C | 30 | 02-Jan-23 |
| C | 30 | 03-Mar-23 |
| C | 0 | 31-Dec-99 |
Thanks!
Hi, Claudio_Prado
According to your description, you want to change the value of the slicer and overwrite the value of [Role]="A" && [Date]="1999/12/31".
First, if you're going to implement several, you need to repeat my approach.
Also, in Power BI, I don't currently have a way to save the values of your slicers in a table.Here are the steps you can refer to :
(1)This is my test data:(2)We need to create a what-if parameter in Power Bi Desktop like this:
(3)Then we need to create two measures:
Measure = var _slicer_value = [Parameter Value] var _role = MAX('Table'[Role]) var _date = MAX('Table'[Date]) return IF(_role="A" && _date = DATE(1999,12,31) , _slicer_value , SUM('Table'[Sum of Value]))Measure 2 = SUMX( ADDCOLUMNS(CROSSJOIN(VALUES('Table'[Role]) ,VALUES('Table'[Date])), "value" , [Measure]) , [value])(4)Then we can put the fields we need on the visual and we can meet your need.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
Hi, Claudio_Prado
According to your description, you want to change the value of the slicer and overwrite the value of [Role]="A" && [Date]="1999/12/31".
First, if you're going to implement several, you need to repeat my approach.
Also, in Power BI, I don't currently have a way to save the values of your slicers in a table.Here are the steps you can refer to :
(1)This is my test data:(2)We need to create a what-if parameter in Power Bi Desktop like this:
(3)Then we need to create two measures:
Measure = var _slicer_value = [Parameter Value] var _role = MAX('Table'[Role]) var _date = MAX('Table'[Date]) return IF(_role="A" && _date = DATE(1999,12,31) , _slicer_value , SUM('Table'[Sum of Value]))Measure 2 = SUMX( ADDCOLUMNS(CROSSJOIN(VALUES('Table'[Role]) ,VALUES('Table'[Date])), "value" , [Measure]) , [value])(4)Then we can put the fields we need on the visual and we can meet your need.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly