Help me to modify current dax to achieve condition.

[Arvind123]

Hi Team,

I have dax like.

Filtered Revenue > 500 =

CALCULATE(

    SUM('BT3 Keyword'[Total revenue]),

    FILTER(

        VALUES('BT3 Keyword'[Keyword]),

        SUMX(

            FILTER('BT3 Keyword', 'BT3 Keyword'[Keyword] = EARLIER('BT3 Keyword'[Keyword])),

            'BT3 Keyword'[Total revenue]

        ) > 500

    )

)

This gives me total value correct at keyword level But when I see on source level there is no value showing.
Where my keyword present in row level, Source in Column and Revenue in Value in matrix visual... It is

displaying value in column total and row total but when source value is less than 500 it is not showing that value
in matrix visual for particular source.
Here are particular keyword which have total value but did not have source value because it has less than 500 revenue
but I need to display that value because for condition if summing of source gives me 500 value then It has to be consider
and it is considering the keryword level but did not display source level revenue,Can someone please help me to modified the dax.
That gives me source level value too.

Something like this I want to achieve:-

Regards,

Arvind

[Arvind123]

Hi @amitchandak ,

Please find the sample data.

Keyword	Revenue	Source
ab	3072.301	A
cd	536.0652	A
avd	829.9001	B
sef	970.6752	B
srfd	1351.724	B
ssww	2676.865	C
fgg	938.9079	C
treaq	7.315659	E
awrdew	2.699883	F
rt4dsa	350.427	F
AWEWSA	3.079823	F
SAFGERQA	1.594021	D
EWFRGEQE	2.123724	D
EWFREWDWQ	3.431873	D
45EGTVDSEW	1.897625	D
FRW	11.6391	D
EWGEW	12.37913	D

Thanks,

Arvind

[amitchandak]

@Arvind123 , You might have used GT for total revenue column

Either try this way (two measures)

M1 = SUMX(
FILTER(allselected('BT3 Keyword'), 'BT3 Keyword'[Keyword] = Max('BT3 Keyword'[Keyword])),
'BT3 Keyword'[Total revenue]
)

Filtered Revenue > 500 =
if(not(isinscope('BT3 Keyword'[Source])),
SUMX(FILTER(
VALUES('BT3 Keyword'[Keyword]),
[M1]> 500
),'BT3 Keyword'[Total revenue]), [M1])

Or (two measures)


M1 = SUM(
'BT3 Keyword'[Total revenue]
)

Filtered Revenue > 500 =
if(not(isinscope('BT3 Keyword'[Source])),
SUMX(FILTER(
VALUES('BT3 Keyword'[Keyword]),
[M1]> 500
),calculate(sum('BT3 Keyword'[Total revenue]))), [M1])

[Arvind123]

Hi @amitchandak ,

Thanks for your quick response!
Here by using your 2nd measure I am getting value of source, But It is also giving me value of those keywords whose total revenue after summing of all source revenue is not greater than 500 also.
I dont want those keywords whose revenue is less than 500.

for your reference this keywords should not be there by condition and by using your dax
the total value is showing blank for those keyword whose revemue is less than 500 where as if total is greater than 500 it is sowing in column total.

Regards,

Arvind

[amitchandak]

@Arvind123 ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Arvind123]

Hi @amitchandak ,

Any modified dax you have for this?

[Arvind123]

Hi @amitchandak ,

Have you received the table?

Thanks,

Arvind

[Arvind123]

Hi Team,
@Anonymous 
@community24 
@Community2 

Can I get modified dax ..
@amitchandakhas provided me the dax like:

M1 = SUM(
'BT3 Keyword'[Total revenue]
)

Filtered Revenue > 500 =
if(not(isinscope('BT3 Keyword'[Source])),
SUMX(FILTER(
VALUES('BT3 Keyword'[Keyword]),
[M1]> 500
),calculate(sum('BT3 Keyword'[Total revenue]))), [M1])

This dax gives me right indication but it is also showing all the keyword who has not surpass the value 500.
The value who has achieved 500 more that gives me in total value for that particular keyword and who has not greater than 500 that shows me blank... So that blank value I don't want in my table.

Thanks,
Arvind