Comparing values in same colums by year and categorie

[jochendecraene]

Hi

I need to create a new calculated column that compares values with the values of a specific categories over several years.

So in the example below I wan to compare the % of AAA for one year with the % of all the other codes for the corresponding years. If it is bigger or equal I want to indicate with "1", if its is smaller "0"

I'm stuck and don't find a proper solution. Can someone have a look? Thnx!

[Ahmedx]

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[jochendecraene]

Thnx!

Can you explain the second part of the code, so I get a better understanding?

RETURN

IF( MAX('Table'[Code])="AAA",0, IF(__AAA<[% pr],1,0))

[Ahmedx]

1. You define a variable __AAA, which calculates the percentage ([% pr]) for the category "AAA" in your table. The CALCULATE function applies filters to the data, using ALLEXCEPT to preserve filtering on the 'Table'[Code] and 'Table'[F Year] columns and adding an additional filter where 'Table'[Code] equals "AAA."

2. Then you have a RETURN statement, which returns a result based on a condition. In this condition, it checks if the maximum code ('Table'[Code]) is equal to "AAA." If it is, it returns 0 (presumably to prevent division by zero). If the code is not "AAA," it goes into the nested condition.

3. Inside the nested condition, it checks whether the value of __AAA is less than your percentage [% pr]. If __AAA is less than [% pr], it returns 1; otherwise, it returns 0. This is likely used to determine whether the percentage for the "AAA" category exceeds a specified threshold.

Overall, this code appears to be part of a larger data model or data query, and its precise meaning depends on the context and the goals of your analysis.