Forum Discussion
Matrix - 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
Hey arlequin71
Just add a timeline slicer like this one: https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380786?tab=Overview
It will be a separate visual, but as long as your data has a date column it will filter your Matrix when you make changes to it.
If you're asking for help with creating your column and row headers I would just create healper columns with IF statements (and maybe FILTER or SUMX formulas) returning your desired result.
If this helps please kudo.
If this solves your problem please accept it as a solution.
Hello Anonymous do you have some reference link to know how to create helper headers columns?
Thanks!
Hey arlequin71
I would need to see a sample of how your data is set up in order to make sure I direct you correctly, but essentially you would create your desired result in a table using your parameters.