Forum Discussion

cookme97's avatar
cookme97
New Member
3 years ago
Solved

Dynamic Spend Annualizer Based on a Slicer

Hello!

Apologies if this has been asked previously - as I am new to DAX and haven't quite found what I'm looking for.

 

Goal: To have a dynamic measure that calculates the annualized spend based on a months slicer across all categories. 
For example, say that I have the following data:

I would like to summarize it in a table and add a measure that calculates the annualized spend (e.g., the average of all the bananas for the first four months multiplied by 12 to get the annualized average.  Or, something like this:

 

Additionally, I would love for it to be dynamic (the annualized spend is based on slicer). So, if I have three months selected and am showing totals by category for three months, the formula might look like [(Sum of all bananas Jan - Mar)/*3 months] * 12 months. 

 

I have been experimenting with SELECTEDVALUE and SUMX but haven't quite figured it out yet. 

 

Thank you so much in advance!

 

Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi cookme97 ,

     

    I think you can try code as below to create a calculated column or a measure.

    APR Run Rate =
VAR _COST =
    CALCULATE ( SUM ( 'Table'[Cost] ) )
VAR _COUNT =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Month] ),
        ALLEXCEPT ( 'Table', 'Table'[Category] )
    )
RETURN
    DIVIDE ( _COST, _COUNT ) * 12

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    3 years ago

    Try this:

    SUM ( Table1[Cost] ) * DIVIDE ( 12, DISTINCTCOUNT ( Table1[Month] ) )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi cookme97 ,

     

    I think you can try code as below to create a calculated column or a measure.

    APR Run Rate =
VAR _COST =
    CALCULATE ( SUM ( 'Table'[Cost] ) )
VAR _COUNT =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Month] ),
        ALLEXCEPT ( 'Table', 'Table'[Category] )
    )
RETURN
    DIVIDE ( _COST, _COUNT ) * 12

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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