Forum Discussion
Matrix - Show values from 2 crossing ranks
- Anonymous6 years ago
Cool. I believe I have all of the information I need now.
First, Create a calculated column with a multiple embedded IF statement to create your Amount Range column. Formula should resemble:
Amount Range = If(AND(Table[Amount]>2000,Table[Amount]<5000, "2k to 5k", If(AND(Table[Amount]>5000,Table[Amount]<8000, "5k to 8k", If(AND(Table[Amount]>8000,Table[Amount]<10000, "8k to 10k", If(Table[Amount]>10000, ">10k", ""))))This will give you a column to use as your "Rows" in the matrix visual.
The next part of this becomes more complicated and while I believe it is acheivable I'm not sure I know how to do it without having access to your data. I have posted a few links below for examples using slicers in measures using either SWITCH or SELECTED VALUE. You will need to create a column with your 0 to 3, 4 to 7, etc in the rows to use as columns in your matrix then follow the examples below to create measures that will give you your desired result:
Switch: https://community.powerbi.com/t5/Desktop/Dynamic-Measures-based-on-filter-selection/td-p/146394
Switch: https://community.powerbi.com/t5/Desktop/Dynamic-Measure-selection-Actual-and-Forecast/td-p/432523
Unpivot data: https://community.powerbi.com/t5/Desktop/Dynamic-Measure-Calculation-Power-BI-DAX/td-p/561851
SELECTEDVALUE: https://docs.microsoft.com/en-us/dax/selectedvalue-function
Hi arlequin71 ,
can you share more on what you wish to create?
For example, I see that in the sample that you have 9 customer distinct IDs and the sum of the amount is 791546 so I would fill the table as below. Is that correct?
How would you fill all the other cells in the table?
LC
| Visit Range | 0 to 3 | 4 to 7 | 8 to 11 | > 12 |
| Amount Range | ||||
| 2k to 5k | ||||
| 5k to 8k | ||||
| 8k to 10k | ||||
| > 10k | 791546 |
The total Amount 791,546 should be apportioned in the corresponden Rows and Columns intersections.
Thanks in advance,