cancel
Showing results for
Did you mean:
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
Super User

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 ) )
)``````
2 REPLIES 2
Super User

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 ) )
)``````
Frequent Visitor

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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!