Age gap difference

[abhishek_2593]

Hi,

i have a dataset of various clubs having teams of different age groups.

i want to calculate if there is more than 2 years age gap between teams under each club.
how can i calculate the age gap?

The dataset is similar to this where U9-U16 are the teams of age groups under9 to under 16.

The condition is there should not be more than 2 years age gap between teams so i need to calculate this.

For Ex: CLub 1 - There is a team in U12 and Team in U16 and the age difference between these two team is 4 which does not satisy my criteria. How do i calculate this in PowerBI?

2. How can i calculate/highlight the clubs which are satisfying the 2 year age gap criteria?

Please help. Thank you!

[Anonymous · Accepted solution]

Hi @abhishek_2593 ,

Suppose we have a table like this:

Make a copy in the Power Query Editor, Replace values.

Select the [Club Name] column, and unpivot the other columns.

Add a new index column. close and apply.

Please new a calculated column:

Column = 
VAR _club = IF( 'Table2'[Value] = 1 , 'Table2'[Club Name])
VAR _index = IF( 'Table2'[Value] = 1 , 'Table2'[Index])
VAR _index1 = _index - 2
VAR _index2 = _index - 1
VAR _index3 = _index + 1
VAR _index4 = _index + 2
VAR _result1 = CALCULATE(SUM('Table2'[Value]),FILTER('Table2','Table2'[Index]=_index1&&'Table2'[Club Name]=_club))
VAR _result2 = CALCULATE(SUM('Table2'[Value]),FILTER('Table2','Table2'[Index]=_index2&&'Table2'[Club Name]=_club))
VAR _result3 = CALCULATE(SUM('Table2'[Value]),FILTER('Table2','Table2'[Index]=_index3&&'Table2'[Club Name]=_club))
VAR _result4 = CALCULATE(SUM('Table2'[Value]),FILTER('Table2','Table2'[Index]=_index4&&'Table2'[Club Name]=_club))
VAR _result = 
SWITCH(
    TRUE(),
    _result1 = 1 , "GREEN",
    _result2 = 1 , "GREEN",
    _result3 = 1 , "GREEN",
    _result4 = 1 , "GREEN",
    "RED"
)
RETURN
_result

Using the calculated columns as a conditional format for the fields of the matrix, the results are shown in the figure.

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous · Accepted solution]

Hi @abhishek_2593 ,

Suppose we have a table like this:

Make a copy in the Power Query Editor, Replace values.

Select the [Club Name] column, and unpivot the other columns.

Add a new index column. close and apply.

Please new a calculated column:

Column = 
VAR _club = IF( 'Table2'[Value] = 1 , 'Table2'[Club Name])
VAR _index = IF( 'Table2'[Value] = 1 , 'Table2'[Index])
VAR _index1 = _index - 2
VAR _index2 = _index - 1
VAR _index3 = _index + 1
VAR _index4 = _index + 2
VAR _result1 = CALCULATE(SUM('Table2'[Value]),FILTER('Table2','Table2'[Index]=_index1&&'Table2'[Club Name]=_club))
VAR _result2 = CALCULATE(SUM('Table2'[Value]),FILTER('Table2','Table2'[Index]=_index2&&'Table2'[Club Name]=_club))
VAR _result3 = CALCULATE(SUM('Table2'[Value]),FILTER('Table2','Table2'[Index]=_index3&&'Table2'[Club Name]=_club))
VAR _result4 = CALCULATE(SUM('Table2'[Value]),FILTER('Table2','Table2'[Index]=_index4&&'Table2'[Club Name]=_club))
VAR _result = 
SWITCH(
    TRUE(),
    _result1 = 1 , "GREEN",
    _result2 = 1 , "GREEN",
    _result3 = 1 , "GREEN",
    _result4 = 1 , "GREEN",
    "RED"
)
RETURN
_result

Using the calculated columns as a conditional format for the fields of the matrix, the results are shown in the figure.

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[abhishek_2593]

Hi Gao,

Thank you! my dataset is something like this.
Only the teams available under a club is in the table.

In this situation, can i use the same steps you suggested?

[Anonymous]

Hi @abhishek_2593 ,

Copy a column and rename it to Index.

replace values and change data type to whole number.

The rest of the calculations remain the same.

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[abhishek_2593]

Hi Gao,

Thank you very much, this is helpful!
But there is small issue where the age gap is shown as red even when there is more teams in the clubs.

For example this is screenshot of my data after applying your measures. Basically clubs with more teams in all age group but still showing red!(some clubs have more than 1 team in same age-group)
How do we deal with this? should we incldude more measures in the column?

Screenhsot of columns i created using your solution,
values are all =1
Index is replacement of ages and converted to whole number 
Column is your measure.

Thanks in advance!

[Anonymous]

Hi @abhishek_2593 ，

Would you please share some sample data that does not contain sensitive data? This will help me to find out the reason faster. Thanks in advance.

How to provide sample data in the Power BI Forum

Best Regards,
Gao
Community Support Team

[abhishek_2593]

Hi @Anonymous ,

Please let me know if you need more data apart from the above screenshots?

Thank you!

[Anonymous · Accepted solution]

Hi @abhishek_2593 ,

Please try this:

Column = 
VAR _club = IF( 'Table2'[Value] <> 0 , 'Table2'[Club Name])
VAR _index = IF( 'Table2'[Value] <> 0 , 'Table2'[Index])
VAR _index1 = _index - 2
VAR _index2 = _index - 1
VAR _index3 = _index + 1
VAR _index4 = _index + 2
VAR _result1 = CALCULATE(SUM('Table2'[Value]),FILTER('Table2','Table2'[Index]=_index1&&'Table2'[Club Name]=_club))
VAR _result2 = CALCULATE(SUM('Table2'[Value]),FILTER('Table2','Table2'[Index]=_index2&&'Table2'[Club Name]=_club))
VAR _result3 = CALCULATE(SUM('Table2'[Value]),FILTER('Table2','Table2'[Index]=_index3&&'Table2'[Club Name]=_club))
VAR _result4 = CALCULATE(SUM('Table2'[Value]),FILTER('Table2','Table2'[Index]=_index4&&'Table2'[Club Name]=_club))
VAR _result = 
SWITCH(
    TRUE(),
    _result1 <> 0 , "GREEN",
    _result2 <> 0 , "GREEN",
    _result3 <> 0 , "GREEN",
    _result4 <> 0 , "GREEN",
    "RED"
)
RETURN
_result

Best Regards,
Gao
Community Support Team

[abhishek_2593]

Hi @Anonymous ,

This works perfectly fine, Thanks a lot for your help 🙂 really appreciate it!
I hope to perfect these skills like you sometime soon!