Create Measure/Calculated Column Dividing Two Sums

[equevedo84]

Good afternoon,

I'm trying to create either a calculated column or measure (whichever works best) to replicate an operation from an Excel report into Power BI.  There is a column for Measures and two other columns for Compliant Members and Eligible members. There are also two category values from the Category column: 4 fields in total.  In Excel I pivot the main dataset and set the Measures to rows and the sum of Compliant and Eligible members.  I then, outside of the pivot table, divide Compliant Members by Eligible Members.  The result is assigned distinctly to each measure from the Measure column in the pitvot table.  Finally I take this division result and vlookup to the Measure value in the main dataset to a new column called Measure Compliance Rate.  How can I replicate this in Power BI?  The dataset is located in the following link to the google drive:

https://docs.google.com/spreadsheets/d/1o8U8KWixik-vk-3xRVVDaUm3fUyEoOhX/edit?usp=drive_link&ouid=11... 

[v-jianpeng-msft · Accepted solution]

Thanks @kpost 
Hi, @equevedo84 

Based on the data you provided, I used the following DAX expression:

Division = 
VAR _compliant_members = SUM('Table'[Compliant Members])
VAR _Eligible_members = SUM('Table'[Eligible Members])
RETURN DIVIDE(_compliant_members,_Eligible_members)

Here are the results:

I've uploaded the PBIX file I used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[equevedo84]

Corrected public link: https://docs.google.com/spreadsheets/d/1o8U8KWixik-vk-3xRVVDaUm3fUyEoOhX/edit?usp=sharing&ouid=11027...

[kpost]

The link you provided is not public.  

[equevedo84]

Whoops, https://docs.google.com/spreadsheets/d/1o8U8KWixik-vk-3xRVVDaUm3fUyEoOhX/edit?usp=sharing&ouid=11027... 

[equevedo84]

FYI - in retrospect the category column is somewhat irrelevant, just the need the calculation done that is specific to the Measures values from the division of the sums of Compliant and Eligible members