cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Sum sales , Category is < 10000

@venug20

 

You can use VISUAL level filters

 

VLF.png

Try my new Power BI game Cross the River

View solution in original post

Highlighted
Super User III
Super User III

Re: Sum sales , Category is < 10000

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 )
Try my new Power BI game Cross the River

View solution in original post

5 REPLIES 5
Highlighted
Frequent Visitor

Re: Sum sales , Category is &lt; 10000

Hi @venug20

Do you want to sort the column in descending order?

 

Thanks,

Rema

Highlighted
Super User III
Super User III

Re: Sum sales , Category is &lt; 10000

@venug20

 

You can use VISUAL level filters

 

VLF.png

Try my new Power BI game Cross the River

View solution in original post

Highlighted
Resolver I
Resolver I

Re: Sum sales , Category is &lt; 10000

Hi Zubair,

 

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

 

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

Highlighted
Super User III
Super User III

Re: Sum sales , Category is &lt; 10000

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 )
Try my new Power BI game Cross the River

View solution in original post

Highlighted
Super User III
Super User III

Re: Sum sales , Category is &lt; 10000

@venug20

 

Please see each page in the attached file

 

 

sumsales.png

 

Try my new Power BI game Cross the River

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors