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
Solution SageHi 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 |
- arlequin71
The total Amount 791,546 should be apportioned in the corresponden Rows and Columns intersections.
Thanks in advance,
- arlequin71
Hello,
Attached the main table Power Query script that is linked to a standard Calendar Table.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZLNasNADITfxecY9LtaHZ0fJxj6BCGHFHotpe9/qOMN2UlvKz40OyPpeh2maRp2QxmX+/cotD5VlWS47a7Dfr9fa7Zx/vpszEIaal1Sxo/7b0NSg8vGDofDWsc4/TyRu2ls6Hg8PhR7l7uiIGv/i50sQZAZfCilAxPtkkxZkCkYqcKB/glih1YBk9GRSCGCiXj/TEmLYgAHRcuUt4m8EEeJ5vF0Oq11do9ixQQQR48tTAxIrBuxVE5kFSaZ8pzI+XzeajBZmFqAeZ63aXWTbmQQG7fNxtnaLpfLPytes25oWZYHCkCUz7bG8OxqOAFidGLpiZIJwY2F3nf6Cl5UHZDC4WnNKNgGl6eyig632x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Fecha = _t, Amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Fecha", type date}, {"Amount", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Fecha", "Date"}}) in #"Renamed Columns"Thanks in advance for your help.
Hey just so I'm clear there are just the three columns? ID, Fecha (Date), and Amount?
And you need the measures to change dynamically with the timeline filter?
Also, the Amount ranges is that calculated by customers individually? It must be otherwise there would only be one line with data by column grouping, but I just want to confirm.
- arlequin71
1. 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!