Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I know this has been answered but I could not figure it out from previous posts...
how can I obtain the following:
Category | Value1 | Value2 | Percent of total | excel formula |
Test1 | 1 | 1 | 0.166667 | F2/G5 |
Test2 | 2 | 2 | 0.333333 | F3/G5 |
Test3 | 2 | 3 | 0.333333 | F4/G5 |
Total | 5 | 6 | 0.277778 | (average) |
from this data set
Category | Value1 | Value2 |
Test1 | 1 | 1 |
Test2 | 1 | 1 |
Test2 | 1 | 1 |
Test3 | 1 | 1 |
Test3 | 0 | 1 |
Test3 | 1 | 1 |
Total | 5 | 6 |
I cannot figure out how to get the percent of total - everything I've tried has either given me a total per row, or a total of the entire Value2; unfiltered (imagine theres test4,test5, etc. that I'm filtering out)
I know it's a silly one, but any and all help is appreciated.
Solved! Go to Solution.
Hi,
Write these measures:
Hi,
Write these measures:
You got it! Thank you again Ashish
You are welcome.
Hi @sdukes
take a look at the following solution:
Percent of Total =
IF (
ISINSCOPE ( 'Table'[Category] ),
DIVIDE ( [Sum of Value2], CALCULATE ( [Sum of Value2], ALL ( 'Table' ) ) ),
AVERAGEX (
'Table',
DIVIDE ( [Sum of Value2], CALCULATE ( [Sum of Value2], ALL ( 'Table' ) ) )
)
)
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
@sdukes - Not entirely certain here:
Percent Total =
DIVIDE(
SUM([Value1]),
SUMX(ALL('Table'),[Value2]),
0
)
That should be the individual rows. After that, you have a measures total problem, Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |