cancel
Showing results for
Did you mean:
Highlighted Anonymous
Not applicable

## SUMX with modifications

Hi,

I have a table with 3 columns, Item, Unit of Measure, and Quantity.

 Item Unit of Measure Quantity A kg 1 B mg 1000000 C mg 1000000 A mg 1000000

I want to add a measure to sum the quantities, but differently depending on the Unit of Measure for each row. If Unit of Measure = "kg", then Quantity X 1000000, else Quantity.

The resulting table would look like the following:

 Item Total A 2000000 B 1000000 C 1000000
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted Super User III

## Re: SUMX with modifications

Hi @Anonymous

Try this, where Table1 is the table you show:

1. Set Table1[ID] in the rows of a matrix visual

2. Create this measure and place it in values of the matrix:

```Measure =
SUMX (
Table1,
IF (
Table1[Unit of Measure] = "kg",
Table1[Quantity] * 1000000,
Table1[Quantity]
)
)```
Highlighted Super User IV

## Re: SUMX with modifications

So, something like:

```Measure =
VAR __table = SUMMARIZE('Table4',[Item],[Unit of Measure],"__quantity",IF([Unit of Measure]="kg",SUM([Quantity])*1000000,SUM([Quantity])))
RETURN
SUMX(__table,[__quantity])```

?

See attached Table 4, Page 3

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

2 REPLIES 2
Highlighted Super User III

## Re: SUMX with modifications

Hi @Anonymous

Try this, where Table1 is the table you show:

1. Set Table1[ID] in the rows of a matrix visual

2. Create this measure and place it in values of the matrix:

```Measure =
SUMX (
Table1,
IF (
Table1[Unit of Measure] = "kg",
Table1[Quantity] * 1000000,
Table1[Quantity]
)
)```
Highlighted Super User IV

## Re: SUMX with modifications

So, something like:

```Measure =
VAR __table = SUMMARIZE('Table4',[Item],[Unit of Measure],"__quantity",IF([Unit of Measure]="kg",SUM([Quantity])*1000000,SUM([Quantity])))
RETURN
SUMX(__table,[__quantity])```

?

See attached Table 4, Page 3

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements #### Community Blog

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

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start. #### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 Top Solution Authors
Top Kudoed Authors
Users online (629)