Count based on revenue amount

[Anonymous]

I need 3 measure like >5, <5, result

basically we need to create >5 how many distinct count of desc based on amount criteria, suggest

example table:

Desc	Amount
A	10
B	4
C	5
D	3
C	2

result :

desc	<=5	>5
a		1
b	1
c	2
d	1

[v-achippa]

Hi @Anonymous,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @danextian and @ryan_mayu for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by ryan resolved your issue? or let us know if you need any further assistance.
If any response resolved your issue, please mark it as "Accept as solution" and give a kudos if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[ryan_mayu]

@Anonymous 

you can try this

>5 = CALCULATE(COUNTROWS('Table'),'Table'[Amount]>5)

measure = CALCULATE(COUNTROWS('Table'),'Table'[Amount]<=5)

then update the measure name to <=5

[danextian · Accepted solution]

Hi @Anonymous 

Assuming Amount is a physical column in your table, the measure would be

CALCULATE (
    DISTINCTCOUNT ( 'table'[desc] ),
    keepfilers ( 'table'[amount] ) <= 5
)

But youre expected result is confusing thouugh. I thought you wanted the distinct count which means that each value in desc should only be counted once - C has 2. If you mean to say the count of records, use:

CALCULATE (
    COUNTROWS ( 'table' ),
    keepfilers ( 'table'[amount] ) <= 5
)

Just change <= to  > for a different calculation.

[Anonymous]

It doesn't works...

Let me clarify , I've 50 million rows so i have summarized table in PBI, you can see sample below.

I need classifcation <5, >5 based on description count & sales.

we need the result based on the input 'summarize table' . suggest on DAX

tried measurement

Test = COUNTAX('table',[amount]>5)

desc <5 = IF([amount]<5,[desc])    //description count

desc >5 = IF([amount]>5,[desc])     //description count

example : sum table in PBI

Desc	Amount
A	10
B	4
C	5
D	3
C	2

result :

desc	<=5	>5
a		1
b	1
c	2
d	1