DAX Calculation --I just wish to calculate or return what is in the red box as this is in relation

[650AA]

Hi I am looking for some advice and guidance on how to calculate the total of the numbers in the red box.

For some context the Top 15 Forecast refers to the Top 15 programmes by highest value and the below DAX to calculate its –

([PivotNewTotalForecast] is a SUM total of all forecast values rows)

The New Total Remit is a sum total of all the remit values, however I just wish to calculate or return what is in the red box as this is in relation with the Top 15 programmes rather than the top15 remit values on their own. Is there a way to do this dynamically ?

Thank you in advance

 @v-jingzhan-msft

[v-kongfanf-msft · Accepted solution]

Hi @650AA ,

In my tests, everything shows up fine. It will return the corresponding results according to the normal topn.
Check if the funnel in the top right corner is filtered by other conditions.

top5 = 
VAR top_ =
    TOPN ( 5, ALLSELECTED ( 'Table'[Program] ), [PivotNewTotalForecast] )
RETURN
     CALCULATE ( [PivotNewTotalForecast], KEEPFILTERS ( top_ ) )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[_AAndrade]

Hi,

Try this on the RETURN:
Calculate ([PivotNewTotalForecast] ),
FILTER('enhancement finance_report (Pivot)',[programme] IN PProgramsTotals)
)

[650AA]

Hi this has not worked, it has just retunr the same values in the Top15forecast colum, it is the NewTotal Remit Column i need to calculate and return what is in the red box as this is in relation with the Top 15 programmes rather than the top15 remit values on their own. Is there a way to do this dynamically ? Thanks

[_AAndrade]

Can you share a sample of your pbix? I could see what I can do

[650AA]

Hi @_AAndrade I have sensitive data in the PBIX, soI am unable to share the file essentially as shown in the red box is total remit amount  I only need to sum the top15 coresponding to the Top15 programmes
So if there is a value for the Top15 programmes by forecast,  then return the remit amount for that Top 15 programme by forecast  not the Top15 programmes by remit, if that males more sense