October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more
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
Solved! Go to Solution.
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êsHi @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êsUser | Count |
---|---|
105 | |
99 | |
98 | |
86 | |
49 |
User | Count |
---|---|
162 | |
142 | |
132 | |
102 | |
63 |