Forum Discussion

cpdanielmc21's avatar
cpdanielmc21
Helper I
6 years ago
Solved

Conditional custom column with some math

Hi community,

Hope you can help me on this one.

 

I want to create a custom column in query editor based on some conditions which include summation and split of amounts. I was able to do this with excel, but can't find a way in powerBI so far...

 

Let me explain: if the sum of amounts for categories 1 and 2 are negative (if they share same month), then category 1 should be zero and the sum of it's amounts must be splited and added into the raws of category 2. Other categories (like 3) untouched.

 

But, if the sum of categories 1 and 2 are positive (if they share month), then categories 1 and 2 must show zero and other categories untouched.

 

Also, number of raws here is very simple, but my data can have hundreds of raws, so the split must be dynamic depending on the number of raws for category 2 and only for the corresponding month. 

 

Any ideas? you think this can be possible?

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi cpdanielmc21 ,

     

    Thanks for explaining, I made this, hope that's what you want:

    Measure 14 = 
VAR sumcategory = CALCULATE(SUM('Table (4)'[Amount]),FILTER(ALL('Table (4)'[Category],'Table (4)'[Amount]),'Table (4)'[Category] in {1,2}))
VAR sumcategory1 = CALCULATE(SUM('Table (4)'[Amount]),FILTER(ALL('Table (4)'[Category],'Table (4)'[Amount]),'Table (4)'[Category] in {1}))
VAR countrow2 = CALCULATE(COUNTROWS('Table (4)'),FILTER(ALL('Table (4)'[Category],'Table (4)'[Amount]),'Table (4)'[Category] = 2))
RETURN IF(MAX('Table (4)'[Category]) = 3,SUM('Table (4)'[Amount]),
        IF(sumcategory < 0,
            IF(MAX('Table (4)'[Category]) = 1,0,sumcategory1 / countrow2 + SUM('Table (4)'[Amount])),
            0))

     

     

    Aiolos Zhao

  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi cpdanielmc21 ,

     

    You can modify Anonymous 's code and create a calculated column directly.

    Column = 
VAR sumcategory = 
CALCULATE(
    SUM('Table (4)'[Amount]),
    FILTER(
       ALL( 'Table (4)'),
        'Table (4)'[Category] in {1,2} && 'Table (4)'[Month] = EARLIER('Table (4)'[Month] )
    )
)
VAR sumcategory1 = 
CALCULATE(
    SUM('Table (4)'[Amount]),
    FILTER(
        ALL('Table (4)'),
        'Table (4)'[Category] = 1 && 'Table (4)'[Month] = EARLIER('Table (4)'[Month] )
    )
)
VAR countrow2 = 
CALCULATE(
    COUNTROWS('Table (4)'),
    FILTER(
        ALL('Table (4)'),
        'Table (4)'[Category] = 2 && 'Table (4)'[Month] = EARLIER('Table (4)'[Month] )
    )
)
RETURN 
IF(
    [Category] = 3,
    'Table (4)'[Amount],
    IF(
        sumcategory > 0,
        0,
        SWITCH(
            [Category],
            1, 0,
            2, (sumcategory1/countrow2) + 'Table (4)'[Amount]
        )
    )
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi cpdanielmc21 ,

     

    You can modify Anonymous 's code and create a calculated column directly.

    Column = 
VAR sumcategory = 
CALCULATE(
    SUM('Table (4)'[Amount]),
    FILTER(
       ALL( 'Table (4)'),
        'Table (4)'[Category] in {1,2} && 'Table (4)'[Month] = EARLIER('Table (4)'[Month] )
    )
)
VAR sumcategory1 = 
CALCULATE(
    SUM('Table (4)'[Amount]),
    FILTER(
        ALL('Table (4)'),
        'Table (4)'[Category] = 1 && 'Table (4)'[Month] = EARLIER('Table (4)'[Month] )
    )
)
VAR countrow2 = 
CALCULATE(
    COUNTROWS('Table (4)'),
    FILTER(
        ALL('Table (4)'),
        'Table (4)'[Category] = 2 && 'Table (4)'[Month] = EARLIER('Table (4)'[Month] )
    )
)
RETURN 
IF(
    [Category] = 3,
    'Table (4)'[Amount],
    IF(
        sumcategory > 0,
        0,
        SWITCH(
            [Category],
            1, 0,
            2, (sumcategory1/countrow2) + 'Table (4)'[Amount]
        )
    )
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi cpdanielmc21 ,

     

    I think using DAX doing the logic is not hard, but i don't get the logic for split amount.

     

     In scenario one, the total amount = -21. Then you want to split it to 2 rows(amount -80 and amount -1)

     

    Why the custom number is -50 and 29?

     

    Could you please help to explain how you get -50 and 29?

     

    Thanks.

    Aiolos Zhao

    • cpdanielmc21's avatar
      cpdanielmc21
      Helper I
      6 years ago

      Anonymous 

      Hi, sure

       

      total for category 1 is 60, so this 60 must be assigned to category 2. 
      Since category 2 has 2 rows, the split is going to be 30 and 30.

       

      -80 plus 30 = -50

      -1 plus 30 = 29

       

       

      so before this process, total for cat 1 and 2 was -21, and after the process is still -21, is just re-arranged.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi cpdanielmc21 ,

         

        Thanks for explaining, I made this, hope that's what you want:

        Measure 14 = 
VAR sumcategory = CALCULATE(SUM('Table (4)'[Amount]),FILTER(ALL('Table (4)'[Category],'Table (4)'[Amount]),'Table (4)'[Category] in {1,2}))
VAR sumcategory1 = CALCULATE(SUM('Table (4)'[Amount]),FILTER(ALL('Table (4)'[Category],'Table (4)'[Amount]),'Table (4)'[Category] in {1}))
VAR countrow2 = CALCULATE(COUNTROWS('Table (4)'),FILTER(ALL('Table (4)'[Category],'Table (4)'[Amount]),'Table (4)'[Category] = 2))
RETURN IF(MAX('Table (4)'[Category]) = 3,SUM('Table (4)'[Amount]),
        IF(sumcategory < 0,
            IF(MAX('Table (4)'[Category]) = 1,0,sumcategory1 / countrow2 + SUM('Table (4)'[Amount])),
            0))

         

         

        Aiolos Zhao

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    cpdanielmc21 , try new columns like


    custom 1 = if([category] in{ 1,2} && [Amount] <0)
    custom 2 = if([category] in{ 1,2} && [Amount] >0)