Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
EaglesTony
Post Prodigy
Post Prodigy

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

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.

View solution in original post

21 REPLIES 21
v-junyant-msft
Community Support
Community Support

Hi @EaglesTony ,

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

vjunyantmsft_0-1699420908082.png

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:

vjunyantmsft_1-1699421080128.png

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.

 






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
Super User
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:

VahidDM_0-1699416328599.png

 

 

 

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

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

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%

 

 

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 ?

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:

VahidDM_0-1699487047416.png

 

 

 

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

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

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

 

EaglesTony_0-1699546920252.png

 

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!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

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:

EaglesTony_0-1699973583865.png

 

 

After selecting Team A:

EaglesTony_1-1699973821134.png

 

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

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.

Hi @EaglesTony ,

Please try this DAX to create a new column:

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

vjunyantmsft_0-1701221953786.png

The results are as follows:

vjunyantmsft_1-1701222010946.png

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.

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

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

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

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

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

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.

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.

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors