Measure

[Sam_2020]

How to write measure of the column header with Sales > 60 and the total value is 100?

Product	Sales	Sales > 60
A	100	100
B	30
C	50
Total	180	100

[nandic]

Hi @Sam_2020  ,
If purpose is to display only max value and to display same value in total, below is formula:

Max Value = IF(MAX(Sheet1[Sales]) = CALCULATE(MAX(Sheet1[Sales]),ALL(Sheet1[Product])),MAX(Sheet1[Sales]))

Cheers,
Nemanja

[Sam_2020]

Thanks Namaja for your reply.

If I have other product which are >60, then how should i write the measure?

Product	Sales	Sales > 60
A	100	100
B	30
C	50
D	75	75
E	90	90
Total	345	265

[ryan_mayu · Accepted solution]

@Sam_2020 

for the second scenario,

please try this

Measure = 
VAR _value=SELECTEDVALUE('Table'[SALES])
return IF(_value>100,_value,SUMX(FILTER('Table','Table'[SALES]>60),'Table'[SALES]))

[Sam_2020]

Thanks. 

It works perfectly.

Are you able to explain "return IF(_value>100;_value;"?

Do I need to look the highest value and indicate in my measure?

[ryan_mayu]

@Sam_2020 

No need to look the highest value. I didn't use Max function.

_value is to select the value(please see VAR function), if >100, then select and display

https://docs.microsoft.com/en-us/dax/var-dax

[ryan_mayu]

@Sam_2020 

Please try this

Measure = IF(SELECTEDVALUE('Table'[SALES])=100,100,SUMX(FILTER('Table','Table'[SALES]=100),'Table'[SALES]))

[kkanda]

Hello Nemanja,  thanks for the response. I am not looking for the MAX value. What I am looking for is creation of a measure by the simple clicking on any bar - that becomes my selected category. If I get that category name as a new measure, I want to create another chart detailing the sales trend of that category over a period of time. Maybe this is wishful thinking - the experts can give their opinion.

kkanda

[kkanda]

Hello All,

My question was diffrent and I do not know how it got clubbed with another query. 

This question is still open and there is no accepted solution. 

Will you please remove the "ACCEPTED SOLUTION" from my original post, so that others will look at it?

Thanks

kkanda

[ryan_mayu]

@kkanda 

Please share your post link or create a new post

.

[kkanda]

This question was answered by Nemanja.

Thanks for all responses.

[nandic]

Hi @kkanda ,

By mistake i tagged you on this question.
I also now answered on your question 🙂