How do I group a field to show on a report

[EaglesTony]

Hi,

 I have the following data:

Team    Item    DaysBackFromToday  

A           123       90

A           456       3

A           789       0

A           412       120

A           892       150

A           105       365

B           501        91

B           502        92

What I need is a field in Dax to show per Team the following

Team A:   0-90 days:  2   90+: 3  %0-90 days:  40% (2/5 is the calc)  %90+: 60%

Team B:   0-90 days:  0   90+: 2  %0-90 days:     0%  %90+: 100%

Thanks

[v-junyant-msft]

Hi @EaglesTony ,

I'll start by showing you the test data I use:

You can use the following DAX to create a measure:
Calculate the days of A on 0-90:

COUNT_A_0-90 = 
CALCULATE(
    COUNT('Table'[DaysBackFromToday]),
    FILTER(
        'Table',
        'Table'[Team] = "A" &&
        'Table'[DaysBackFromToday] > 0 &&
        'Table'[DaysBackFromToday] <= 90
    )
)

Calculate the days of A on 90+:

COUNT_A_90+ = 
CALCULATE(
    COUNT('Table'[DaysBackFromToday]),
    FILTER(
        'Table',
        'Table'[Team] = "A" &&
        'Table'[DaysBackFromToday] > 90
    )
)

Calculate the total number of A's, excluding cases where days=0:

Total_A_>0 = 
CALCULATE(
    COUNT('Table'[DaysBackFromToday]),
    FILTER(
        'Table',
        'Table'[Team] = "A" &&
        'Table'[DaysBackFromToday] > 0
    )

Calculate the proportion of days in A on 0-90:

Percentage_A_0-90 = 
[COUNT_A_0-90] / [Total_A_>0]

Calculate the proportion of days in A on 90+:

Percentage_A_90+ = 
[COUNT_A_90+] / [Total_A_>0]

And the final output is shown in the following figure:

For B, you just need to change the A in each measure to B, and I won't repeat it for you.

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EaglesTony]

Thanks, but I don't think this will work, as I have a variable amount of teams at any one point, meaning I could have 2 teams or 20 teams when I do the refresh.

[VahidDM]

Hey @EaglesTony 

Use these measures:

0-90 days = 
Var _A = FILTER('Table','Table'[Days Back From Today]<90)
Return
COUNTROWS(_A)+0

90+ days = 
Var _A = FILTER('Table','Table'[Days Back From Today]>90)
Return
COUNTROWS(_A)+0

% 0-90 days = 
Var _A = FILTER('Table','Table'[Days Back From Today]<90)
Var _B = COUNTROWS(_A)+0
Var _C = [0-90 days]+[90+ days]
Return
DIVIDE(_B,_C)

%90 days = 
Var _A = FILTER('Table','Table'[Days Back From Today]>90)
Var _B = COUNTROWS(_A)+0
Var _C = [0-90 days]+[90+ days]
Return
DIVIDE(_B,_C)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[EaglesTony]

I got the wrong requirements, it shouldn't be a count but a total based on a column called points.

So for the following:

Team    Item    DaysBackFromToday       Points  

A           123       90                                  3

A           456       3                                    2

A           789       0                                    5

A           412       120                                1

A           892       150                                5

A           105       365                                5

B           501        91                                 8 

B           502        92                                 2

It should be:

Team A:   0-90 days:  10 (pts)   90+: 11 (pts)  %0-90 days:  48% (10/21 is the calc)  %90+: 52%

Team B:   0-90 days:  0 (pts)  90+: 10 (pts)  %0-90 days:     0%  %90+: 100%

[EaglesTony]

I think I might need to create these in M instead of DAX ?

Maybe have a "Y" or "N" column for LessThan90 and Over90 and use that somehow in the table on the UI ?

[VahidDM]

Hey @EaglesTony 

Ok, just updated those measures as below:

90+ days = 
Var _A = FILTER('Table','Table'[DaysBackFromToday]>90)
Return
SUMX(_A,[Points])+0

0-90 days = 
Var _A = FILTER('Table','Table'[DaysBackFromToday]<=90)
Return
SUMX(_A,[Points])+0

%90 days = 
Var _B = [90+ days]
Var _C = [0-90 days]+[90+ days]
Return
DIVIDE(_B,_C)

% 0-90 days = 
Var _B = [0-90 days]
Var _C = [0-90 days]+[90+ days]
Return
DIVIDE(_B,_C)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[EaglesTony]

Thanks, the strange thing is that when I put this field in the table in the report, the teams are showing the correct information, but there is a row that is blank (FYI: I have a slicer using Team name):

[VahidDM]

Hey @EaglesTony 

Remove that +0 from 90+ days and 0-90 days measures, that will be fixed

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[EaglesTony]

What I did was in the Filters don't show blank teams.

Now it is working incorrectly when trying to use my slicer selecting team is is showing all teams still:

Before selecting Team:

After selecting Team A:

[v-junyant-msft]

Hi @EaglesTony 

Have you solved your problem?
Can you please provide the pbix file if it hasn't been resolved? It's hard to tell what the problem is just from your description.

Best Regards,
Dino Tao

[EaglesTony]

Hi,

  No my problem still persists when I add the slicer, not sure why, as it started after I added these fields to the report.

  One thing I did find out is that when I have put the whole number fields on the report, it doesn't impact the slicer, however when I put in a percetange value on the report, it causes the issue of showing all the teams, regardless of the slicer value.

  I can't send over the .pbix due to company policy.

[v-junyant-msft]

Hi @EaglesTony ,

Please try this DAX to create a new column:

Group = 
IF (
    'Table'[DaysBackFromToday] >= 0
        && 'Table'[DaysBackFromToday] <= 90,
    "0-90 days",
    "90+ days"
)

The results are as follows:

If you are still having problems, please point out the problem in the pbix file I provided.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EaglesTony]

Hi,

  I have the columns created correctly, the issue is with the slicer, when I select a single team and if the report only has whole numbers (i.e. DaysBackFromToday), it works in only showing that team, however when I select a single team and the report has percentages (i.e. PctWithin90Days), it shows the single teams values AND all the other teams data as blank, but their percentage as 0%)

[v-junyant-msft]

Hi @EaglesTony ,

I know your issue is currently on slicer, and I'm concerned that it may be caused by the DAX you were using earlier.
Since I don't see the situation you're experiencing with the DAX I'm using myself, I hope you can try a different DAX and see if the slicer is still having problems.
If the problem persists, please continue to follow the thread.

Best Regards,
Dino Tao

[EaglesTony]

I would think I need a new DAX for the percentage calculation as well ?

[v-junyant-msft]

Hi @EaglesTony ,

Have you tried a new measure for you situation and has the problem been solved?
If it doesn't solve the problem, I suggest you to use the pbix that I provided earlier and test your need, the problem you are facing didn't occur in my test situation.

Best Regards,
Dino Tao

[EaglesTony]

I assume I would use a DAX query to build the percentage column as well ?

[v-junyant-msft]

Hi @EaglesTony ,

No need to create a separate measure to get the percentage.
Please follow the steps below:
Create a calculated column for grouping (0-90 and 90+), and the DAX I have provided before;
Place the point column in the Value column and use the aggregation function and the display format (SUM, show column total) to display the summary value as well as the percentage.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EaglesTony]

I used your DAX query for Group and I put this field "Group" on my table visual, however it is giving me 2 rows now for each team I select (1 for 90+ days and 1 for 0-90 days) and I don't see any percentage.

The issue seems to be tied to a percentage column using in conjuntion with the Team slicer.

[EaglesTony · Accepted solution]

I was able to figure this out.

I needed to use the DIVIDE with alternate result instead of using an If condition, so I had to use  the DAX DIVIDE.