cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

Calculating a percentage based on two measures over time

Hi all,

 

I'm struggling to wrap my head around the following.

 

My report looks like this:

 

Question4.JPG

EDIT: PBI file can be found here.

 

The source tables are the three on top: Products, Entries & Durations.

 

Now, I created two (quite complex) measures based on the data in those source tables and a separate calendar table (which is referred to by the slicer on top). Those measures are represented in the bottom graph, over time: 'Measure for Visual X' & 'Measure for Visual Y'. 

 

What I need to do now, is create a KPI that calculates a percentage of X / (X + Y), so in the example above it would be 0,6/(2+1,4 + 0,6) = 15%. 

 

As you can see, I tried two variations, but I'm unable to get to the desired 15% result:

 

This one gives 20%:

 

Percentage = DIVIDE([Measure for Visual Y],[Measure for Visual X]+[Measure for Visual Y])

 

 

And this one gives 43%:

 

Percentage (AVG) = DIVIDE(AVERAGEX(Entries,[Measure for Visual Y]),AVERAGEX(Entries,[Measure for Visual X])+AVERAGEX(Entries,[Measure for Visual Y]))

 

 

Any ideas on how to get 15%? 😵

1 ACCEPTED SOLUTION
Super User IV
Super User IV

Please try this expression that returns 15%

 

New Measures =
VAR x =
SUMX ( 'calendar', [Measure for Visual X] )
VAR y =
SUMX ( 'calendar', [Measure for Visual Y] )
RETURN
DIVIDE ( y, x + y )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
Super User IV
Super User IV

Please try this expression that returns 15%

 

New Measures =
VAR x =
SUMX ( 'calendar', [Measure for Visual X] )
VAR y =
SUMX ( 'calendar', [Measure for Visual Y] )
RETURN
DIVIDE ( y, x + y )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

@mahoneypat 


Beautiful. Thank you! 😊

Super User IV
Super User IV

@zudar , this should be like

Percentage (AVG) = DIVIDE([Measure for Visual Y],A[Measure for Visual X]+Entries,[Measure for Visual Y])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak 

 

I think something went wrong when you typed out the formula. 😶

@zudar , Just use the measure no need of nay aggregation on top of it

Percentage (AVG) = DIVIDE([Measure for Visual Y],[Measure for Visual X]+[Measure for Visual Y])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors