How to filter a measure on certain condition or count values of a measure

[gauri]

Hello,

I have a measure which gives me sales on the count of selection on the slicer for month. I have another measure where i have put a condition that where my sales would be above 300 display it as 1 else 0. Now how do i sum up all the 1's in a measure.

Posting teh DAX below for more clarity :

sales measure =

var a = divide(sum(sales[NetSales]),DATEDIFF(MAX('Calendar'[Start Year]),MAX('Calendar'[Date]),MONTH) +1 )

var b = IF(a>=300, 1, 0)

How do i complete this measure where i can get the count of 1's or is there any other way of doing this

Thanks

Gauri P

[v-yiruan-msft · Accepted solution]

Hi @gauri ,

You can create a measure as below:

New Measure =
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[groupfileds],
        "dsales",
            DIVIDE (
                SUM ( sales[NetSales] ),
                DATEDIFF ( MAX ( 'Calendar'[Start Year] ), MAX ( 'Calendar'[Date] ), MONTH ) + 1
            )
    )
RETURN
    SUMX ( FILTER ( _tab, [dsales] >= 300 ), [Sales] )

If the above one is not applicable for your scenario, please provide some sample data of sales and Calendar table and your expected result with exact example or screenshot. Thank you.

Best Regards

[amitchandak]

@gauri , For that you need a grouo by or level of details where you can do that. It can be date customer, item

example

var b = countx(values(Sales[column]),IF(a>=300, 1, 0))

column is the column you have to replace