topic Re: Powerbi Desktop - Line item value Vs. Grand Total in DAX Commands and Tips

Powerbi Desktop - Line item value Vs. Grand Total

Anonymous — Tue, 13 Oct 2020 21:45:42 GMT

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!

Re: Powerbi Desktop - Line item value Vs. Grand Total

vivran22 — Wed, 14 Oct 2020 01:45:50 GMT

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

Re: Powerbi Desktop - Line item value Vs. Grand Total

Anonymous — Wed, 14 Oct 2020 03:44:01 GMT

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!

Re: Powerbi Desktop - Line item value Vs. Grand Total

wdx223_Daniel — Wed, 14 Oct 2020 03:55:09 GMT

try this measure

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

Re: Powerbi Desktop - Line item value Vs. Grand Total

vivran22 — Thu, 15 Oct 2020 00:36:32 GMT

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