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.
Hi All,
I am new to PowerBI & DAX, also first time joining this community, hope you can help me with a question.
This is the raw data set also the dataset reflected in PowerBI:
Outcome | Attempts | Initiative |
A | 1 | Tiger |
B | 2 | Bear |
C | 2 | Tiger |
C | 1 | Tiger |
C | 2 | Tiger |
D | 3 | Lion |
E | 1 | Tiger |
F | 1 | Fish |
F | 4 | Birds |
G | 3 | Birds |
H | 2 | Birds |
H | 1 | Reptiles |
H | 3 | Reptiles |
In excel, the table has been pivoted into the following form:
Outcome | |||||||||
Attempts | A | B | C | D | E | F | G | H | Grand Total |
1 | 1 | 1 | 1 | 1 | 1 | 5 | |||
2 | 1 | 2 | 1 | 4 | |||||
3 | 1 | 1 | 1 | 3 | |||||
4 | 1 | 1 | |||||||
Grand Total | 1 | 1 | 3 | 1 | 1 | 2 | 1 | 3 | 13 |
I would like to calculate a rate by dividing the grand total of column A+B+C+D (N = 6) by the grand total (N= 13). The answer should be 0.461538.
May I know how to do that in PowerBI? I have tried to use SUMX, but it doesnt seem to work.
Please help if you can, I would appreciate it. Thank you.
Solved! Go to Solution.
It sounds like you want something like this:
Rate =
DIVIDE (
CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( VALUES ( Table1[Outcome] ), Table1[Outcome] IN { "A", "B", "C", "D" } )
),
CALCULATE ( COUNTROWS ( Table1 ), ALL ( Table1 ) )
)
It sounds like you want something like this:
Rate =
DIVIDE (
CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( VALUES ( Table1[Outcome] ), Table1[Outcome] IN { "A", "B", "C", "D" } )
),
CALCULATE ( COUNTROWS ( Table1 ), ALL ( Table1 ) )
)
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |