cancel
Showing results for
Did you mean:

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

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
Community Champion

@venug20

You can use VISUAL level filters

Regards
Zubair

Community Champion

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

5 REPLIES 5

Hi @venug20

Do you want to sort the column in descending order?

Thanks,

Rema

Community Champion

@venug20

You can use VISUAL level filters

Regards
Zubair

Resolver I

Hi Zubair,

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

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

Community Champion

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

Community Champion

@venug20

Please see each page in the attached file

Regards
Zubair

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Fabric Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors