Forum Discussion

Gladiator909's avatar
Gladiator909
Icon for Helper I rankHelper I
3 years ago
Solved

Understanding Percentages - Calculating future value

Hi guys,

 

I am stuck on what should be a pretty straight forward dax measure. I am hoping I can get some clarity on how the percentages work in Power BI. Here is my table:

 

Marble ColorPrevious AmountRecoveredProcessed
Blue2002050
Green30040120
Red15010100
Yellow1003060

 

I am calculating the recovery rate as follows: 

 

Recovery Rate = divide(sum('Table'[Recovered]),sum('Table'[Processed])) 
And it is a percentage
 
And now I need to calculate the "projected value": [Previous Amount] * Recovery Rate
 
This is the DAX for that: 
Projection = sum('Table'[Previous Amount]) * [Recovery Rate]
 
Here is the visual that this gives me:
 

 

What I would like the "Total Projection" card to return instead is (80 + 100 + 15 + 50) = 245

 
Now I do understand that the way Power BI is calculating the recovery rate is basically summing all the recovered marbles and dividing by all the processed marbles and coming up with a % and using that % value to return me 227.27
 
Any ideas how to modify the DAX (Projection = sum('Table'[Previous Amount]) * [Recovery Rate]) 
so that it returns 245 instead?
 
I know I can create one dax for each marble color and add it all up but that would mean loosing the ability to use a slicer on the chart.
 
Any help would be greatly appreciated
 
Thanks



Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • LuizKoller's avatar
    LuizKoller
    3 years ago

    Hey,

     

    Try using SUMX:

     

    Total Projection =
    SUMX('Table', [Recovery Rate] * 'Table'[Previous Amount])



    This evaluate line by line and sum each time.

1 Reply

  • LuizKoller's avatar
    LuizKoller
    Icon for Resolver I rankResolver I
    3 years ago

    Hey,

     

    Try using SUMX:

     

    Total Projection =
    SUMX('Table', [Recovery Rate] * 'Table'[Previous Amount])



    This evaluate line by line and sum each time.