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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.