Powerbi Desktop - Line item value Vs. Grand Total

[Anonymous]

Folks

I have the following columns in my report

Industry                                Watch             Rating

Agri                                       4.3 %                   1

Mining                                   3.2  %                  1

Manufacturing                       1.4  %                  1

Oil & Gas                                6.7  %                 2

GRAND TOTAL                       5.6 %

The Watch is computed using a formula. What I need is if the individual Watch  item value > Grand Total, then 2, else 1, as 'Rating'.  Is there a way I can store the Grand Total in a 'global' kind of variable, and then compare the individual row value against this global variable and return 1 or 2?

Thanks in advance!

[wdx223_Daniel · Accepted solution]

try this measure

Rating=VAR _WatchTotal=CALCULATE([Watch],ALL('industry'[industry])) RETURN IF([Watch]<_WatchTotal,1,2)

[vivran22]

Hey @Anonymous ,

As per your description, Grand Total of watch is based on a formula, and I am guessing it is a measure. 

If this is true, then you can use the measure in your formula:

If this is not the case, then please share details on how you are calculating the Grand Total.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Anonymous]

Hi @vivran22 

Thanks for your reply.  The Watch is a calculated measure Watch (%) = (sum('Industry'[gloan]+sum('Industry'[wloan]))/sum('Industry'[CurrVal]), based on the Industry columns.  As its a matrix report, the Grand Total is enabled from the Format Painter (Sub Totals 'on') and the value is computed and there's  no Grand Total measure as such. 

Thanks!

[vivran22 · Accepted solution]

@Anonymous 

You may try the following measure:

Rating = 
VAR _Overall =
    CALCULATE ( [Watch %], ALL ( Industry[Industry] ) )
VAR _Rating =
    IF ( HASONEVALUE ( Industry[Industry] ), IF ( [Watch %] > _Overall, 2, 1 ) )
RETURN
    _Rating

where 

Watch % = 
DIVIDE (
    SUM ( Industry[gloan] ) + SUM ( Industry[wloan] ),
    SUM ( Industry[CurrVal] )
)

Result:

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[wdx223_Daniel · Accepted solution]

try this measure

Rating=VAR _WatchTotal=CALCULATE([Watch],ALL('industry'[industry])) RETURN IF([Watch]<_WatchTotal,1,2)