Forum Discussion

equevedo84's avatar
equevedo84
Frequent Visitor
2 years ago
Solved

Create Measure/Calculated Column Dividing Two Sums

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=110279400869480562245&rtpof=true&sd=true 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

5 Replies