cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## DAX to calculate relative and total percentage growth based on multiple time selections

I'm trying to calculate few DAX measure to calculate the total balance change between two selected time periods for different codes. The possible outcome would look like:

I have calculated the first 4 DAX measure as follows:

``````Time Period 1 = VAR _d1 = MIN(data[Date])
RETURN CALCULATE(SUM(data[Balance]), ALLEXCEPT(data,data[Code]),DATESINPERIOD(data[Date], _d1, -6, MONTH)) - CALCULATE(SUM(data[Balance]), ALLEXCEPT(data,data[Code]),DATESINPERIOD(data[Date], _d1, -3, MONTH))``````
``````Time Period 2 = VAR _d1 = MAX(data[Date])
RETURN CALCULATE(SUM(data[Balance]), ALLEXCEPT(data,data[Code]),DATESINPERIOD(data[Date], _d1, -6, MONTH)) - CALCULATE(SUM(data[Balance]), ALLEXCEPT(data,data[Code]),DATESINPERIOD(data[Date], _d1, -3, MONTH))``````
``Growth = [Time Period 2]-[Time Period 1]``
``% Growth = DIVIDE([Growth],[Time Period 2])``

I created the following DAX for total growth:

``````Total Growth = var _d1 = MAX(data[Date])
RETURN CALCULATE([% Growth], data[Date]=_d1,ALL(data))``````

But I'm sure something's wrong with the formula as I'm not getting any values:

Theoritically 'total growth' is simply the overall growth for all the codes combined(23.21%) in this case which in turn is used to calculate 'relative growth' = % growth - total growth. I'm not able to capture the total growth primarily because it's calculated based on 2 time periods.

Please suggest appropriate modifications to the DAX.

1 ACCEPTED SOLUTION
Super User

Hi, @Anonymous

I am not sure if I understood correctly, but do you want to show 23.21% for all rows, including the total?

Then, try the below,

CALCULATE([% Growth], ALLSELECTED(data[Code]))

Sorry for my poor understanding.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Visit my LinkedIn page by clicking here.

Schedule a meeting with me to discuss further by clicking here.

2 REPLIES 2
Super User

Hi, @Anonymous

I am not sure if I understood correctly, but do you want to show 23.21% for all rows, including the total?

Then, try the below,

CALCULATE([% Growth], ALLSELECTED(data[Code]))

Sorry for my poor understanding.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Visit my LinkedIn page by clicking here.

Schedule a meeting with me to discuss further by clicking here.

Anonymous
Not applicable

Thanks a lot @Jihwan_Kim this worked like a charm. I always get confused with the 'ALL' function family.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors