Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
@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.
Nathaniel
Proud to be a Super User!
Hi @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.
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!
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.
Nathaniel
Proud to be a Super User!
Oh, sorry
Sum of ZZ+02 should be (300+600)=900
The table is amended
Thanks for correction
@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.
Nathaniel
Proud to be a Super User!
May I ask further, It is possible that not create a column to do so?
Hi @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.
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!
Hi @Nathaniel_C
This is equation I modified.
Sum of 2 (New) = VAR _CustList = CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1 ) RETURN CALCULATE ( SUM ( 'Count'[Amount] ), 'Count'[Customer] IN ( _CustList ), 'Count'[Code] = 2)
I'm so curious why it's empty for (ZZ, Sum of 2 (New) ) and the total is correct, it's very very strange. how come?
Like all measures that act differently than we think it should it's all about filter context. If we take the line for Item NN as an example:
The first part of your measure
VAR _CustList = CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1 )
gets the filter context of [Item] = NN (from the line in the visual) and you add the filter [Code] = 1. This returns a list of customers, in this case A, B, C (those are the only customers with [Item] NN and [Code] 1.
Then the second line
CALCULATE ( SUM ( 'Count'[Amount] ), 'Count'[Customer] IN ( _CustList ), 'Count'[Code] = 2)
Calculates the SUM and applies 3 filters.
1. The [Item] filter from the visual of NN
2. The list from VAR _CustList: (A, B, C)
3. [Code] = 2 which only appears on lines for customers (D, E)
So you are asking it to SUM ( 'Count'[Amount] ) for customers (A, B, C) from these lines:
Customer | Code | Item | Amount |
E | 2 | NN | 1400 |
D | 2 | NN | 1100 |
No lines match all the criteria so we get a blank.
Hi @Anonymous ,
We ran into the same thing.
Here is the first solution and the subsequent solution that @jdbuchanan71 sent. The first one had exactly the same issue. Not sure why.
First one: Measure = VAR _CustList = INTERSECT ( CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1), CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 2) ) RETURN CALCULATE ( SUM ( 'Count'[Amount] ), 'Count'[Customer] IN ( _CustList ), KEEPFILTERS( 'Count'[Code] = 2 ) ) Second one: Measure = 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 ) )
Nathaniel
Proud to be a Super User!
Perfect, exactly what I want, thanks so much
Hi @Anonymous ,
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!
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.
Nathaniel
Sum of 1 = var _code = 1 var _calc = CALCULATE([Sum of Amount],'Count'[Code]=1) return _calc
Proud to be a Super User!
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |