Forum Discussion
Creating tables from a dataset
Hi Anonymous 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 Anonymous
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
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 Anonymous
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.