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 ...
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.

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.