cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arlequin71
Helper II
Helper II

Matrix - Show values from 2 crossing ranks

Hello, i'm looking for a best way to create a Matrix table like the attached example that can be filtered by date.

I have tried using measures ranks and columns ranks but  i could't obtain desired results.

 

These are the metrics i need to cross in matrix:

# Visits range = # of customers ID in a period   // 0 to 3 ; 4 to 7 ; 8 to 11 ; more than 12

 

Amount Range = Total amount in a period  //  2000 to 5000 ; 5001 to 8000 ; 8001 to 10000; > 100002020-02-24_10h21_45.png

Thanks a lot in advance for your help.

 

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"

 

 

 

 

1 ACCEPTED SOLUTION

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

View solution in original post

12 REPLIES 12
lc_finance
Solution Sage
Solution Sage

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  

 

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.

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!

 

Is the amount in the range a summation of individual lines that fall within that range?

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

OK, i will read the links you shared and try.   

Thanks a lot for your help.

The total Amount 791,546 should be apportioned in the corresponden Rows and Columns intersections.

 

Thanks in advance,

 

 

Tad17
Solution Sage
Solution Sage

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 @Tad17  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.

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors