cancel
Showing results for
Did you mean:
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:

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

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!

5 REPLIES 5
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!

Helper V

Beautiful. Thank you! 😊

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])

Proud to be a Super User!

Helper V

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

Super User IV

@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])

Proud to be a Super User!

Announcements

#### Happy New Year from Power BI

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