Forum Discussion

jochendecraene's avatar
jochendecraene
Icon for Helper V rankHelper V
2 years ago
Solved

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 yea...
Need Help
Show and Tell
  • Ahmedx's avatar
    Ahmedx
    2 years ago

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

     

jochendecraene's avatar
jochendecraene
Icon for Helper V rankHelper V
2 years ago

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's avatar
Ahmedx
Icon for Super User rankSuper User
2 years ago

  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.