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

% of base year

Hi, I would like to create a measure % of Base year, based on the launch year I select in a slicer (see below an example with launch year 2012).

I tried to calculate a measure "revenue launch year", as a mid-step, but I failed to generate/populate the numbers colored red (239k). Do you know how to generate such formula or to calculate the "% of base" in some other way?

 Year LaunchYear Revenue Revenue Launch Year % of Base 2017 2012 6,167 239,032 2.6% 2016 2012 37,505 239,032 15.7% 2015 2012 57,548 239,032 24.1% 2014 2012 80,872 239,032 33.8% 2013 2012 104,734 239,032 43.8% 2012 2012 239,032 239,032 100.0%

Thank you

1 ACCEPTED SOLUTION
Super User

Hi @Anonymous,

Create a table with the years that you want to use as base I have made a calculated table based on DAX with the following formula:

`LaunchYear = DISTINCT(Revenue[Year])`

Then use this table as your slicer and use the following measures to calculate your values:

```RevenueLaunchYear = var Launch_Year = MAX(LaunchYear[Year])
Return
CALCULATE(SUM(Revenue[Revenue]); Revenue[Year] = Launch_Year)

% of Base = SUM(Revenue[Revenue])/[RevenueLaunch Year]```

The RevenueLaunchYear measure is not necessary to have you can only make this single formula.

```% of Base =
VAR Launch_Year =
MAX ( LaunchYear[Year] )
VAR RevenueLaunchYear =
CALCULATE ( SUM ( Revenue[Revenue] ); Revenue[Year] = Launch_Year )
RETURN
SUM ( Revenue[Revenue] ) / RevenueLaunchYear```

Another note to this is that by using MAX if you don't select any value on the slicer it will give you the greater value (2017) when you select some value the calculations will be made accordingly.

See below the result and attached PBIX file.

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User

Hi @Anonymous,

Create a table with the years that you want to use as base I have made a calculated table based on DAX with the following formula:

`LaunchYear = DISTINCT(Revenue[Year])`

Then use this table as your slicer and use the following measures to calculate your values:

```RevenueLaunchYear = var Launch_Year = MAX(LaunchYear[Year])
Return
CALCULATE(SUM(Revenue[Revenue]); Revenue[Year] = Launch_Year)

% of Base = SUM(Revenue[Revenue])/[RevenueLaunch Year]```

The RevenueLaunchYear measure is not necessary to have you can only make this single formula.

```% of Base =
VAR Launch_Year =
MAX ( LaunchYear[Year] )
VAR RevenueLaunchYear =
CALCULATE ( SUM ( Revenue[Revenue] ); Revenue[Year] = Launch_Year )
RETURN
SUM ( Revenue[Revenue] ) / RevenueLaunchYear```

Another note to this is that by using MAX if you don't select any value on the slicer it will give you the greater value (2017) when you select some value the calculations will be made accordingly.

See below the result and attached PBIX file.

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

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.