How to divide values from Same column based on filter

[mohanpal]

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. 

[liuqi_pbi · Accepted solution]

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!

[StefanoGrimaldi]

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