cancel
Showing results for
Did you mean:
Highlighted Helper III

## Transformed Table Measure

Hi Expert,

I would like to have 2 measures that to transform table

Raw data is like this

 Customer Code Item Amount A 01 MM 100 A 01 NN 200 A 02 ZZ 300 B 01 MM 400 B 01 NN 500 B 02 ZZ 600 B 02 MM 650 C 01 MM 700 C 01 NN 800 C 01 ZZ 900 D 02 MM 1000 D 02 NN 1100 D 02 ZZ 1200 E 02 MM 1300 E 02 NN 1400 E 02 ZZ 1500

Measure 1: Sum of 01
Measure 2: Sum of 02 (based on the customer have code "01")

to get the expected result as this

 Sum of 01 Sum of 02 MM 1200 (100+400+700) 650 NN 1500(200+500+800) 0 ZZ 900 900 (300+600)

Many thanks

Simon

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted Super User IV

## Re: Transformed Table Measure

Ok then! Added a column to check on whether the customer had 1. Here is my pbix. Count of 1 and 2

```Sum of 2 =

var _code = 2
var _calc = CALCULATE([Sum of Amount],'Count'[Code]=2,'Count'[IF Customer has 1]>=1)

return _calc``` Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Proud to be a Super User!

Highlighted Super User IV

## Re: Transformed Table Measure

This is the new Sum of 2, which does not need the Calculated Column.  Had to go to another guy on the leaderboards @jdbuchanan71 .

Whew!

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

```Sum of 2 =
VAR _CustList =
CALCULATETABLE (
INTERSECT (
CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1 ),
CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 2 )
),
ALLEXCEPT ( 'Count', 'Count'[Customer] )
)
RETURN
CALCULATE (
SUM ( 'Count'[Amount] ),
'Count'[Customer] IN ( _CustList ),
KEEPFILTERS ( 'Count'[Code] = 2 )
)```

Proud to be a Super User!

12 REPLIES 12
Highlighted Super User IV

## Re: Transformed Table Measure

Here is the first part. Not sure what you want for the second part. Will look at it again.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel ```Sum of 1 =
var _code = 1
var _calc = CALCULATE([Sum of Amount],'Count'[Code]=1)

return _calc```

Proud to be a Super User!

Highlighted Super User IV

## Re: Transformed Table Measure

Hey @SimonChung_GGGG ,

Shouldn't it be A2ZZ = 300

B2ZZ = 600 and B2MM = 650?
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Proud to be a Super User!

Highlighted Helper III

## Re: Transformed Table Measure

Oh, sorry

Sum of ZZ+02 should be (300+600)=900

The table is amended

Thanks for correction

Highlighted Super User IV

## Re: Transformed Table Measure

Ok then! Added a column to check on whether the customer had 1. Here is my pbix. Count of 1 and 2

```Sum of 2 =

var _code = 2
var _calc = CALCULATE([Sum of Amount],'Count'[Code]=2,'Count'[IF Customer has 1]>=1)

return _calc``` Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Proud to be a Super User!

Highlighted Super User IV

## Re: Transformed Table Measure

Calculated column is

`IF Customer has 1 = CALCULATE(COUNTROWS('Count'),FILTER(ALLEXCEPT('Count','Count'[Customer]),'Count'[Code]=1))`

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Proud to be a Super User!

Highlighted Helper III

## Re: Transformed Table Measure

Hi @Nathaniel_C , It's help, Great thanks!

Highlighted Helper III

## Re: Transformed Table Measure

May I ask further, It is possible that not create a column to do so?

Highlighted Super User IV

## Re: Transformed Table Measure

This is the new Sum of 2, which does not need the Calculated Column.  Had to go to another guy on the leaderboards @jdbuchanan71 .

Whew!

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

```Sum of 2 =
VAR _CustList =
CALCULATETABLE (
INTERSECT (
CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1 ),
CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 2 )
),
ALLEXCEPT ( 'Count', 'Count'[Customer] )
)
RETURN
CALCULATE (
SUM ( 'Count'[Amount] ),
'Count'[Customer] IN ( _CustList ),
KEEPFILTERS ( 'Count'[Code] = 2 )
)```

Proud to be a Super User!

Highlighted Helper III

## Re: Transformed Table Measure

Perfect, exactly what I want, thanks so much

Announcements #### Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers! #### June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news. #### 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 (1,077)