topic Re: How do I group a field to show on a report in DAX Commands and Tips

How do I group a field to show on a report

EaglesTony — Tue, 07 Nov 2023 19:09:51 GMT

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

Re: How do I group a field to show on a report

VahidDM — Wed, 08 Nov 2023 04:05:41 GMT

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

Re: How do I group a field to show on a report

Anonymous — Wed, 08 Nov 2023 05:25:28 GMT

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.

Re: How do I group a field to show on a report

EaglesTony — Wed, 08 Nov 2023 13:23:42 GMT

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.

Re: How do I group a field to show on a report

EaglesTony — Wed, 08 Nov 2023 18:12:08 GMT

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%

Re: How do I group a field to show on a report

EaglesTony — Wed, 08 Nov 2023 15:53:50 GMT

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 ?

Re: How do I group a field to show on a report

VahidDM — Wed, 08 Nov 2023 23:44:19 GMT

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

Re: How do I group a field to show on a report

EaglesTony — Thu, 09 Nov 2023 16:22:08 GMT

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):

Re: How do I group a field to show on a report

VahidDM — Thu, 09 Nov 2023 22:30:23 GMT

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

Re: How do I group a field to show on a report

EaglesTony — Fri, 10 Nov 2023 13:01:27 GMT

How do I do that ?

Re: How do I group a field to show on a report

EaglesTony — Tue, 14 Nov 2023 14:57:14 GMT

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:

Re: How do I group a field to show on a report

Anonymous — Mon, 27 Nov 2023 09:42:12 GMT

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

Re: How do I group a field to show on a report

EaglesTony — Tue, 28 Nov 2023 15:24:19 GMT

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.

Re: How do I group a field to show on a report

Anonymous — Wed, 29 Nov 2023 01:41:34 GMT

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.

Re: How do I group a field to show on a report

EaglesTony — Wed, 29 Nov 2023 12:09:52 GMT

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%)

Re: How do I group a field to show on a report

Anonymous — Thu, 30 Nov 2023 01:57:29 GMT

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

Re: How do I group a field to show on a report

EaglesTony — Thu, 30 Nov 2023 12:59:06 GMT

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

Re: How do I group a field to show on a report

Anonymous — Tue, 05 Dec 2023 02:14:53 GMT

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

Re: How do I group a field to show on a report

EaglesTony — Tue, 05 Dec 2023 12:26:17 GMT

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

Re: How do I group a field to show on a report

Anonymous — Wed, 06 Dec 2023 00:55:12 GMT

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.