cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

## Re: SUMX with modifications

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]
)
)```
Super User

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

Proud to be a Datanaut!

2 REPLIES 2
Super User

## Re: SUMX with modifications

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]
)
)```
Super User

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