cancel
Showing results for
Did you mean:
New Member

## 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!

1 ACCEPTED SOLUTION
Community Support

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 2
Community Support

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.

Super User

Try this:

``````SUM ( Table1[Cost] ) * DIVIDE ( 12, DISTINCTCOUNT ( Table1[Month] ) )
``````

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors