Forum Discussion

mlleverino's avatar
mlleverino
Helper I
9 years ago
Solved

Retrieving last count recorded

Need help with DAX instruction to do the following.   REPORTING PERIOD         REVENUES      TOOL COUNT (as of this month) 2016-04                            1,000               2 2016-04        ...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi mlleverino

     

    Here are three calculated measures that might be getting closer.  I don't think I fully understand the Tool Count measure but this is what I have for your sample data.  It might only be a slight tweak away from what you need.

     

    Tool Count = CALCULATE(
                DISTINCTCOUNT('Table1'[Amount]),
                'Table1'[Account Type] ="Systems"
                )


Revenue = CALCULATE(
                SUM('Table1'[Amount]),
                'Table1'[Account Type] ="Revenue"
                )

Result = DIVIDE([Revenue],[Tool Count])

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi mlleverino

 

Thanks for that.  In this case is the BU name the same as the Tool Count?

 

I'm guessing the Amount column = Revenue

mlleverino's avatar
mlleverino
Helper I
9 years ago

Account Type - Systems = tool count for that reporting period and for that BU

Account Type - Revenue = revenues for that reporting period and for that BU.

 

if I were to look at a one BU for one reporting period, it will have 1 tool count entry and 1 revenue entry.

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi mlleverino

     

    Here are three calculated measures that might be getting closer.  I don't think I fully understand the Tool Count measure but this is what I have for your sample data.  It might only be a slight tweak away from what you need.

     

    Tool Count = CALCULATE(
                DISTINCTCOUNT('Table1'[Amount]),
                'Table1'[Account Type] ="Systems"
                )


Revenue = CALCULATE(
                SUM('Table1'[Amount]),
                'Table1'[Account Type] ="Revenue"
                )

Result = DIVIDE([Revenue],[Tool Count])