cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Transformed Table Measure

Hi Expert,

 

I would like to have 2 measures that to transform table

 

Raw data is like this

 

CustomerCodeItemAmount
A01MM100
A01NN200
A02ZZ300
B01MM400
B01NN500
B02ZZ600
B02MM650
C01MM700
C01NN800
C01ZZ900
D02MM1000
D02NN1100
D02ZZ1200
E02MM1300
E02NN1400
E02ZZ1500

 

 

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 01Sum of 02
MM1200 (100+400+700)650
NN1500
(200+500+800)
0
ZZ900900 (300+600)

 

 

Many thanks

Simon

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Transformed Table Measure

@SimonChung_GGGG ,

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

 

sum of 2.PNG


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





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

Proud to be a Super User!




View solution in original post

Highlighted
Super User IV
Super User IV

Re: Transformed Table Measure

Hi @SimonChung_GGGG ,

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




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

Proud to be a Super User!




View solution in original post

12 REPLIES 12
Highlighted
Super User IV
Super User IV

Re: Transformed Table Measure

Hi @SimonChung_GGGG ,

 

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.PNG

 

 

 

 

 

 

Sum of 1 = 
var _code = 1
var _calc = CALCULATE([Sum of Amount],'Count'[Code]=1)


return _calc

 

 

 

 





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

Proud to be a Super User!




Highlighted
Super User IV
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





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

Proud to be a Super User!




Highlighted
Helper III
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
Super User IV

Re: Transformed Table Measure

@SimonChung_GGGG ,

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

 

sum of 2.PNG


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





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

Proud to be a Super User!




View solution in original post

Highlighted
Super User IV
Super User IV

Re: Transformed Table Measure

Hi @SimonChung_GGGG ,

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





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

Proud to be a Super User!




Highlighted
Helper III
Helper III

Re: Transformed Table Measure

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

Highlighted
Helper III
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
Super User IV

Re: Transformed Table Measure

Hi @SimonChung_GGGG ,

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




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

Proud to be a Super User!




View solution in original post

Highlighted
Helper III
Helper III

Re: Transformed Table Measure

Perfect, exactly what I want, thanks so much

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

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

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

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

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors