How to use DAX in PowerBI according to a pivot table result in Excel?

[Cze-Jhin]

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.

[AlexisOlson · Accepted 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 ) )
)

[Cze-Jhin]

Hi @AlexisOlson , thank you very much for your help! I truly appreciate it!