Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
Hoping someone can help me here. I'm trying to re-create the excel table below in a PBI Matrix chart. I have the # values, but am having trouble building a % measure that will give me the % of each value compared to the subtotal.
Excel Table I'm trying to reproduce:
What I have in PowerBI so far:
As you can see, the % columns just show 100% for everything.
Anyone have any ideas as to how I can get those perentages to be a % of the subtotal, labeled here as "Total"?
Any help would be greatly appreciated - thank you.
Solved! Go to Solution.
I'm going to be honest - I thought because of my own capabilities that this wouldn't work, but it was PERFECT. This is what my formula looks like:
% Onboarding = DIVIDE(CALCULATE( [# Onboarding]),CALCULATE([# Onboarding],ALLSELECTED('Contract User'[Onboarding_Healthscore_Flag_Color__c])),0)
and it worked perfectly. Thanks Sean, much appreciated - you just made my day.
Give this a try...
% Measure = DIVIDE ( SUM ( Table[# Outstanding] ), CALCULATE ( SUM ( Table[# Outstanding] ), ALLSELECTED ( Table[Flag Color] ) ), 0 )
So if you already have a Measure for the # Outstanding just subsititute with it in the above fomula.
Or if you count # Outstanding change the SUM above to the function you are actually using.
Good Luck!
HTH!
EDIT: ALLEXCEPT would not work in a Matrix if in addition to the Rows you also have a Column field which it appears you do.
It will only give you the correct % in the Total Column at the end.
I'm going to be honest - I thought because of my own capabilities that this wouldn't work, but it was PERFECT. This is what my formula looks like:
% Onboarding = DIVIDE(CALCULATE( [# Onboarding]),CALCULATE([# Onboarding],ALLSELECTED('Contract User'[Onboarding_Healthscore_Flag_Color__c])),0)
and it worked perfectly. Thanks Sean, much appreciated - you just made my day.
@BKnecht try calculating the total per region using allexcept
val = calculate(sum([buyer]), allexcept(tabl1, tabl1[region])
this will be your denominator.
User | Count |
---|---|
141 | |
70 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |