Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@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

 

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

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




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
Nathaniel_C
Super User
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





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

Proud to be a Super User!




Anonymous
Not applicable

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

 

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!




Anonymous
Not applicable

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




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

Proud to be a Super User!




Anonymous
Not applicable

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?

 

image.png

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





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

Proud to be a Super User!




Anonymous
Not applicable

Perfect, exactly what I want, thanks so much

Anonymous
Not applicable

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

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





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

Proud to be a Super User!




Nathaniel_C
Super User
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.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!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors