Forum Discussion
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
Anonymous ,
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.
NathanielHi Anonymous ,
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.
NathanielSum 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 ) )
12 Replies
Hi Anonymous ,
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.
NathanielSum of 1 = var _code = 1 var _calc = CALCULATE([Sum of Amount],'Count'[Code]=1) return _calc
Hey Anonymous ,
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.
NathanielAnonymous ,
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