cancel
Showing results for
Did you mean: 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 Super User

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]
)
) 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

I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

2 REPLIES 2 Super User

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]
)
) 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

I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Announcements Top Kudoed Authors
Users Online
Currently online: 400 members 3,223 guests
Recent signins:
• wheilman • appsac • ChrisMendoza • bwhitlock • BIW • matemusic • lscoffield • Francag • vchevalier • camorri • tlitvinenko • ShaunBuchan • ivostammis • Tanme 