Sum sales , Category is Less than; 10000 (Using DAX Only)

[venug20]

Hi Every one,

I have table like this

Category	Sales
a	20000
b	9000
c	8000
d	7000
e	6000
f	5000
g	4000
h	3000

I want to show my table like below (sum of Sales should be less than 10000)

Category	Sales
b	9000
c	8000
d	7000
e	6000
f	5000
g	4000
h	3000
Totals	42000

Can you help on this... Thanks in Advance

[Zubair_Muhammad · Accepted solution]

@venug20

You can use VISUAL level filters

[rema]

Hi @venug20

Do you want to sort the column in descending order?

Thanks,

Rema

[Zubair_Muhammad · Accepted solution]

@venug20

You can use VISUAL level filters

[venug20]

Hi Zubair,

Thanks for responding, you have given perfect solution for this... 

I want same result using "DAX"...... Can you help on this.....

[Zubair_Muhammad · Accepted solution]

HI @venug20

Using DAX there could be three ways of doing this

1) Calculated Table

2) Measure

3) Calculated Column

1) CALCULATED TABLE

From the Modelling Tab>>New Table

Calculated_Table =
CALCULATETABLE (
    Table1,
    FILTER (
        VALUES ( Table1[Category] ),
        CALCULATE ( SUM ( Table1[Sales] ) < 10000 )
    )
)

2) MEASURE

Measure =
IF (
    HASONEFILTER ( Table1[Category] ),
    IF ( SUM ( Table1[Sales] ) < 10000, SUM ( Table1[Sales] ) ),
    SUMX (
        VALUES ( Table1[Category] ),
        IF (
            CALCULATE ( SUM ( Table1[Sales] ) ) < 10000,
            CALCULATE ( SUM ( Table1[Sales] ) )
        )
    )
)

3) CALCULATED COLUMN

Sales < 10000 =
VAR result =
    CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Category] ) )
RETURN
    IF ( result < 10000, result )

[Zubair_Muhammad]

@venug20

Please see each page in the attached file