cancel
Showing results for
Did you mean:
Highlighted
Resolver I

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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III

## Re: Sum sales , Category is &lt; 10000

@venug20

You can use VISUAL level filters

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

## Re: Sum sales , Category is &lt; 10000

@venug20

You can use VISUAL level filters

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

## 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
Highlighted
Super User III

## Re: Sum sales , Category is &lt; 10000

@venug20

Please see each page in the attached file

Try my new Power BI game Cross the River

Announcements

#### August 2020 Community Challenge: Can You Solve These?

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

#### Community Blog

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

#### 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!

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

Top Solution Authors
Top Kudoed Authors