Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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?

 

YearLaunchYearRevenueRevenue Launch Year% of Base
201720126,167239,0322.6%
2016201237,505239,03215.7%
2015201257,548239,03224.1%
2014201280,872239,03233.8%
20132012104,734239,03243.8%
20122012239,032239,032100.0%

 

Thank you

1 ACCEPTED SOLUTION
MFelix
Super User
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.

baseyear.gif

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
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.

baseyear.gif

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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