Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
using this:
percent = DIVIDE(SUM('Table1 (2)'[People]), CALCULATE(COUNT('Table1 (2)'[People]),ALLEXCEPT('Table1 (2)','Table1 (2)'[People])))
I was able to get a percentage but the calculation needs to work within the first group. Otherwise the percentage is wrong as it works on the total of people and not the sub totals.
How do get the calculations to work within groups?
ie Y1 Y2 Y3
Group JOB people % people % people %
first 1 50 50
2 50 50
Total 100 100
Second 3 75 75
4 25 25
Total 100 100
Total 200
Hi @Mart,
You could use the DAX below.
Measure = sum(Table2[Amount])/CALCULATE(SUM(Table2[Amount]),ALLEXCEPT(Table2,Table2[Group]))
Sample data
Regards,
Charlie Liao
Charlie
Thats ecactly what I want, but I cant repeate it I will start again from scratch.
Could it be because I have it in a matrix?
Mart
@Mart, Could you please provide us more information why you cannot do it? If possible providue us some screenshot, so that we can make further analysis.
Regards,
Charlie Liao
Cracked it! At least I have the correct numbers
I used 3 new measures
First New Measure
GrandTotal = CALCULATE(SUM(Table2[People]),GROUPBY(Table2,Table2[Group]),ALLEXCEPT(Table2,Table2[Year],Table2[People]))
Second New Measure
PeopleSum = SUM(Table2[People])
Third New Measure
Percent = Table2[PeopleSum]/Table2[GrandTotal]
Question
Is there an easier way?
hi, you need to modified the AllExcept, Just mantain the filter Group and it works
% = DIVIDE ( CALCULATE ( COUNT ( Table1[People] ) ), CALCULATE ( COUNT ( Table1[People] ), ALLEXCEPT ( Table1,Table1[Group] ) ) )
Thanks Victor
Almost there but percentage still wrong after first total row, you can't have more than 100%
Will try to upload new image
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |