Forum Discussion

mohanpal's avatar
mohanpal
Frequent Visitor
4 years ago
Solved

How to divide values from Same column based on filter

Hi, I have this sample data. I want to create a column 'Percentage' which should divide based on ID and Year. I did manage to write a query but that is aggreating all the projection value and giving me one single value not vaue for each row. 

Need Help
  • liuqi_pbi's avatar
    liuqi_pbi
    4 years ago

    Hi mohanpal 

     

    If you accept a DAX method, you can create a calculated column with below DAX. This will add a new column to the table. 

    Percentage =
VAR _projectionA =
    MAXX (
        FILTER (
            'Table',
            'Table'[Id] = EARLIER ( 'Table'[Id] )
                && 'Table'[Year] = EARLIER ( 'Table'[Year] )
                && 'Table'[Label] = "A"
        ),
        'Table'[Projection]
    )
VAR _projectionB =
    MAXX (
        FILTER (
            'Table',
            'Table'[Id] = EARLIER ( 'Table'[Id] )
                && 'Table'[Year] = EARLIER ( 'Table'[Year] )
                && 'Table'[Label] = "B"
        ),
        'Table'[Projection]
    )
RETURN
    DIVIDE ( _projectionA, _projectionB )

     

    ----------------------------------------------------------------------

    If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!

2 Replies

  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    Resident Rockstar
    4 years ago

    remember its recommended to get a quicker answer to show a end result of the result you looking to have, quickly as I can see and understand I recommend you using a "group by" of the power query that lets you decide the operation to be done with a certain value column (sum, divei, multipli, count, etc) and group the operation by giving columns as the 2 you need. 

     

    hope this helps of get you a little on your way

  • liuqi_pbi's avatar
    liuqi_pbi
    Resolver III
    4 years ago

    Hi mohanpal 

     

    If you accept a DAX method, you can create a calculated column with below DAX. This will add a new column to the table. 

    Percentage =
VAR _projectionA =
    MAXX (
        FILTER (
            'Table',
            'Table'[Id] = EARLIER ( 'Table'[Id] )
                && 'Table'[Year] = EARLIER ( 'Table'[Year] )
                && 'Table'[Label] = "A"
        ),
        'Table'[Projection]
    )
VAR _projectionB =
    MAXX (
        FILTER (
            'Table',
            'Table'[Id] = EARLIER ( 'Table'[Id] )
                && 'Table'[Year] = EARLIER ( 'Table'[Year] )
                && 'Table'[Label] = "B"
        ),
        'Table'[Projection]
    )
RETURN
    DIVIDE ( _projectionA, _projectionB )

     

    ----------------------------------------------------------------------

    If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!