Forum Discussion
Helper IIMatrix - Show values from 2 crossing ranks
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
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
Helper IIOK, i will read the links you shared and try.
Thanks a lot for your help.