The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Have a dataset with unpivoted column like this
Region | Subscription | Attribute | Value |
East | Bronze | Q1 | 5 |
East | Bronze | Q2 | 3 |
West | Bronze | Q1 | 4 |
West | Bronze | Q2 | 6 |
East | Silver | Q1 | 10 |
East | Silver | Q2 | 12 |
West | Silver | Q1 | 5 |
West | Silver | Q2 | 2 |
East | Gold | Q1 | 7 |
East | Gold | Q2 | 7 |
West | Gold | Q1 | 8 |
West | Gold | Q2 | 9 |
On this by quarter data in matrix using #'s can be shown without isssues as below
Subscription | Bronze | Bronze | Silver | Silver | Gold | Gold |
Region | Q1 | Q2 | Q1 | Q2 | Q1 | Q2 |
East | 5 | 3 | 10 | 12 | 7 | 7 |
West | 4 | 6 | 5 | 2 | 8 | 9 |
but i want to see them in % by quarter like below, how to achieve this, Kindly help
Subscription | Bronze | Bronze | Silver | Silver | Gold | Gold |
Region | Q1 | Q2 | Q1 | Q2 | Q1 | Q2 |
East | 23% | 14% | 45% | 55% | 32% | 32% |
West | 24% | 35% | 29% | 12% | 47% | 53% |
@Anonymous and @amitchandak - Here is the dataset, in the description, have mentioned incorrectly
Dataset
Hi @Bala_Robin ,
Here are the steps you can follow:
1. Create measure.
Measure =
var _groupsum=
SUMX(
FILTER(ALL('Table'),
'Table'[Region]=MAX('Table'[Region])&&'Table'[Attribute]=MAX('Table'[Attribute])),[Value])
return
DIVIDE(
MAX('Table'[Value]),_groupsum)
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks @Anonymous, in this custom sort needed (Bronze, Silver and Gold). Tried using sort order as column but that creates circular dependency and having another reference table (Bronze, Silver and Gold) with the sort order doesn't calculates the % (in fact it gives everything as 100%)
@Bala_Robin , Not able to get the logic. Please elaborate
Subtotal logic should help
Percent of Total and Percent of SubTotal https://www.youtube.com/watch?v=6jTildcV2ho
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=24270s
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
User | Count |
---|---|
77 | |
76 | |
36 | |
31 | |
29 |
User | Count |
---|---|
93 | |
79 | |
57 | |
48 | |
48 |