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

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