Forum Discussion

Dave1982's avatar
Dave1982
Frequent Visitor
4 years ago
Solved

Cost Per Member Calculation By Month

Hello,

I am relatively new to Power BI and DAX and having trouble writing this Dax code for what I feel is rather simple formula.

 

I am looking to be able to do a calculation for Cost Per Member for multiple years. I am looking to divide total Operation Costs by Total Active Members for the month. Members can be part of 1 of 3 groups" Associates, Community, and Contractors.

 

So if the Total Operation Expenses for January is $5000 and there are 600 active members for January I want to divide the two values  by themselves to get the cost of $8.33 per member.

 

I want to be able to incorporate slicers to be able to change from months and years and I have not been able to come up with a solution for that. I would like to have the report default to the latest month of sales and member data and default to December when the previous year is selected. 

 

Any help is so appreciated. Thanks in advance for any assistance. Both Tables are attached for being able to understand the mock data that I am using. 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Dave1982 

    Thanks for reaching out to us.

    You can try this measure,

    avg = 
var _members=CALCULATE(SUM(Table2[Member Count]),FILTER(ALL(Table2),Table2[Year]=MIN(Table1[Year]) && Table2[Quarter]=MIN(Table1[Quarter]) && Table2[Month]=MIN(Table1[Month])))
return DIVIDE(MIN(Table1[Total Op Ex]),_members)

     

    Best Regards,

    Community Support Team _Tang

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

3 Replies

  • ChristianDGreat's avatar
    ChristianDGreat
    Icon for Resolver I rankResolver I
    4 years ago

    There's tons of ways to do this and what I recommend is create a date table first and use that to connect those two table together. Once you do you can simply create a dax formula like 

    DIVIDE (
         SUM(Total OP EX) /

         SUM(Member Count), 

         0
    )

     

     

    If you want quick and easy, you can simly create a relationship using the date column on both tables. Then apply the formula above.

    • Dave1982's avatar
      Dave1982
      Frequent Visitor
      4 years ago

      Thanks for the response, but my issues is when the data is not sliced that it adds up all the members instead of just the year. So if I were to choose 2020 as the year than it would add up all the months when these overlap. It would divide by the total of 2685 versus just pulling the latest month of April only.. Hopefully that makes sense. 

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Dave1982 

    Thanks for reaching out to us.

    You can try this measure,

    avg = 
var _members=CALCULATE(SUM(Table2[Member Count]),FILTER(ALL(Table2),Table2[Year]=MIN(Table1[Year]) && Table2[Quarter]=MIN(Table1[Quarter]) && Table2[Month]=MIN(Table1[Month])))
return DIVIDE(MIN(Table1[Total Op Ex]),_members)

     

    Best Regards,

    Community Support Team _Tang

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