Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
DAX. CALCULATE
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    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

  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    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 )
    )
Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Oh, sorry

 

Sum of ZZ+02 should be (300+600)=900

 

The table is amended

 

Thanks for correction

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    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

    • Nathaniel_C's avatar
      Nathaniel_C
      Community Champion
      6 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Nathaniel_C , It's help, Great thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      May I ask further, It is possible that not create a column to do so?

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        6 years ago

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