Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Dear All,
I have below concern and please do assit on this. I have a data table similart to tale no 1. which has main category and sub categories under each main category for each month and their respective values.
Category | SubCategory | Month | Value |
A | a | 01-Jan-24 | 100 |
A | b | 01-Jan-24 | 200 |
A | c | 01-Jan-24 | 100 |
B | a | 01-Jan-24 | 350 |
B | b | 01-Jan-24 | 250 |
A | a | 01-Feb-24 | 250 |
A | b | 01-Feb-24 | 150 |
A | c | 01-Feb-24 | 200 |
B | a | 01-Feb-24 | 350 |
B | b | 01-Feb-24 | 150 |
by using the row data it is very easy to create the table below using matrix table,
Category | Sub category | 01-Jan-24 | 01-Feb-24 | Total |
A | 400 | 600 | 1000 | |
a | 100 | 250 | 350 | |
b | 200 | 150 | 350 | |
c | 100 | 200 | 300 | |
B | 600 | 500 | 1100 | |
a | 350 | 350 | 700 | |
b | 250 | 150 | 400 | |
Total | 1000 | 1100 | 2100 |
but what i want it to generate below table as the final result which when for each subcategory, the percentage should calculate within the main category and sum up to 100% within the main category. where the total percentage of main categories sum up to 100% seperately for each month.
Category | 01-Jan-24 | 01-Feb-24 | Total | |
A | 40% | 55% | 48% | |
a | 25% | 42% | 35% | |
b | 50% | 25% | 35% | |
c | 25% | 33% | 30% | |
B | 60% | 45% | 52% | |
a | 58% | 70% | 64% | |
b | 42% | 30% | 36% | |
Total | 100% | 100% | 100% |
much appreciated your solutions towards this.
Thanking in advance.
Solved! Go to Solution.
You are welcome. PBI file attached.
Hope this helps.
@Ashish_Mathur ,
Thank you so much for the response, this helps with the subcategory %. can you help me to show the maincategory level % as below without changing the what i highlighted as correct.
example : what is ticked is correct, need to change the main category A=40%, B=60% for the month of Jan
You are welcome.
@Ashish_Mathur ,
Thank you so much for the response, this helps with the subcategory %. can you help me to show the maincategory level % as below without changing the what i highlighted as correct.
example : what is ticked is correct, need to change the main category A=40%, B=60% for the month of Jan
@LahiruFernando , You can use the inbuilt functionality of "Percent of column total". Just click "Down arrow "on your value column and choose the below option so you'll get the expected output.
@Tahreem24 ,
Thank you so much for your solution, I have tried this before and it will give me the main category level percentage split correctly sum up to 100%. but what I want is at the same time to calculate the subcategory level % within the category, where the total % within main category of subcategories should sum up to % as well. so then I can see what is the contribution form each sub category to the main category.
if you will be able to generate, please kindly assist
Thank you
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
89 | |
86 | |
75 | |
69 | |
68 |
User | Count |
---|---|
220 | |
128 | |
117 | |
82 | |
77 |