Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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!
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!
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.