cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Comparing values in same colums by year and categorie

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!

1 ACCEPTED SOLUTION
Super User

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

4 REPLIES 4
Super User

Super User

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

Helper IV

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))
Super User
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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.