Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi Everyone! Below is my dataset.
Month | Session | 12M | 15M | 18M |
Jan | AM | L1 | L2 | L2 |
Jan | AM | L1 | L1 | L1 |
Jan | PM | L2 | L1 | L1 |
Feb | AM | L1 | L1 | L1 |
Feb | PM | L1 | L1 | L1 |
Mar | AM | L1 | L1 | L1 |
Mar | PM | L2 | L2 | L2 |
Mar | PM | L1 | L1 | L1 |
And this is my desired output:
I want to create an automated table by visualizing it in matrix using power BI. Here is what i achieved so far:
It somehow calculates each L1 and L2 as whole year's value. Tried changing the DAX formulas, still the same. Could anyone provide me guidance on this? You help is much apreciated!!
Solved! Go to Solution.
Hi @KainLittleCloth First unpivot your table. See images below:
Your provided data set:
Open power query, select Month and Session column, Right click and select unpivot other columns. See image below:
Here is the final table output:
Close and load table, select matrix visual and populate data. Here is the desired output:
Format according to your need.
Hope this helps!!
If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar Hafiz
In the first step, in order to work with the table effectively, it’s recommended to perform an Unpivot in Power Query.
This will turn it into a vertical table, making it more flexible for analysis.
Then after closing and apply you can create the matrix :
The pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hello @KainLittleCloth
To obtain the desired outcomes, the source dataset must be transformed. Utilize the following PowerQuery script to perform the transformation:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8lQ0lEKTi0uzszPA7IMjXxBpCmYtACRYDXB+UUlSrE60UpeiSBVjiAJH0MQYQQjDLHLwwlk+QBfuFY0ebfUJBz6jZDkA3DK+yYW4dBvjCSPZD+cwJTH0B8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, Column1 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month", type text}, {"Session", type text}, {"12M", type text}, {"15M", type text}, {"18M", type text}, {"MonthSort", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month", "Session", "MonthSort"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Tenor"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each {"L1","L2","No Fix"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Custom", "Headers"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"MonthSort", "Tenor", "Month", "Session", "Headers", "Value"}),
#"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Count", each if [Value] = [Headers] then 1 else null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Count", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Value"})
in
#"Removed Columns"
After transformation, the dataset will appear as follows:
Subsequently, arrange the components as depicted in the screenshot below:
This approach should rectify your issues. However, if it does not, please furnish additional details about your data model and any constraints you may have.
The guidance provided is contingent upon the information you supplied in your query.
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
In the first step, in order to work with the table effectively, it’s recommended to perform an Unpivot in Power Query.
This will turn it into a vertical table, making it more flexible for analysis.
Then after closing and apply you can create the matrix :
The pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi @KainLittleCloth
It seems like you are using 2 disconnected tables without a relationship between them...
To help more I need to see the pbix.
Please save it in some kind of public cloud and share a link.
@Ritaf1983 You were right! I was using 2 different tables, so i recreated a new one and managed to get the results i needed.
Anyway, I face 2 more issues:
1. is it possible if i only want the "Total" per month and per quarter? Its annoying that it only has the option to show all total or not show at all.
2. I would like to add a "NA" as how i displayed in my desired output (NA is when there is no L1 or L2) Some of the months does not have L2 values thus it is not showing L2 but i want it to show a blank space instead, how can i do it?
Desired output:
Many thanks!
Hi @KainLittleCloth
According to subtotals, you can select the level that you need to show from here :
video guide :
https://www.youtube.com/watch?v=jMUO_m9CkPM
(From 19 minute)
According to NA ...it depends of your data if you have a rows than is not L1/L2 you can create a "show column" which will conditionally give the name "NA" for all the rows than not L1/L2
There is a lot of methods to fo it.
For example from PQ :
then use this column in the matrix :
The updated pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi @Ritaf1983 , thankyou so much for your method. Sorry for the late reply, i have just tried it. Another 2 follow up questions here: How do i show the columns even there is not values in it?
This is how mine looks like:
The "NA" does not show because all of them has values either for L1 or L2, but i would like to see the column to be there even though there is no value.
My desired output:
Question 2: How do i show a summary for each quarter like this?
Really apreciate your time and effort!!
Just to add on, I tried selelecting "Show items with no data" in the values field for all but nothing changes.
Hi @KainLittleCloth First unpivot your table. See images below:
Your provided data set:
Open power query, select Month and Session column, Right click and select unpivot other columns. See image below:
Here is the final table output:
Close and load table, select matrix visual and populate data. Here is the desired output:
Format according to your need.
Hope this helps!!
If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar Hafiz
Hi @shafiz_p ,
Thank you for your solution! It works now, but i face 2 more issues after this, i replied my issue above under Ritaf's solution.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |