Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
venug20
Resolver I
Resolver I

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

Hi Every one,

 

I have table like this

 

CategorySales
a20000
b9000
c8000
d7000
e6000
f5000
g4000
h3000

 

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

 

CategorySales
b9000
c8000
d7000
e6000
f5000
g4000
h3000
Totals42000

 

Can you help on this... Thanks in Advance

 

 

 

2 ACCEPTED SOLUTIONS

@venug20

 

You can use VISUAL level filters

 

VLF.png


Regards
Zubair

Please try my custom visuals

View solution in original post

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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

5 REPLIES 5
rema
Advocate I
Advocate I

Hi @venug20

Do you want to sort the column in descending order?

 

Thanks,

Rema

@venug20

 

You can use VISUAL level filters

 

VLF.png


Regards
Zubair

Please try my custom visuals

Hi Zubair,

 

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

 

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

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 )

Regards
Zubair

Please try my custom visuals

@venug20

 

Please see each page in the attached file

 

 

sumsales.png

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.