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
Helper II1. Yes, there are only 3 columns.
Visit Ranges are built from Count of ID and Ammount Range from Amont column.
2. Yes, measures should change according to time filter
3. Amount ranges are calculated globally for all customers.
Thanks!
Is the amount in the range a summation of individual lines that fall within that range?
- arlequin71
Each intersection cell fall within specific Column and Row range.
Due # Visits = Count ID, then the Amount in the Matrix reflect the sum of amount for each ID.
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
- arlequin71
OK, i will read the links you shared and try.
Thanks a lot for your help.