cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper V

## How do I group a field to show on a report

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

1 ACCEPTED SOLUTION
Helper V

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.

21 REPLIES 21
Community Support

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.

Helper V

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.

Super User

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.

Helper V

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%

Helper V

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 ?

Super User

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.

Helper V

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

Super User

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.

Helper V

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:

Community Support

Hi @EaglesTony

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

Helper V

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.

Community Support

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.

Helper V

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

Community Support

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.

Best Regards,
Dino Tao

Helper V

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

Community Support

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

Helper V

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

Community Support

Hi @EaglesTony ,

No need to create a separate measure to get the percentage.
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.

Helper V

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.

Helper V

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors