cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RichardP
Helper I
Helper I

Grouping values from measures

Hi,

 

I have a table that looks something like this:

 

AccountIDUserIDVisitsHelp requests
abc11150
abc45601
abc789152
def55522
def66601

 

I then have a measure that calculates a 'health score' for each account based on how many visits the users from each account make to the website and how many times they ask for help.

 

When I display the measure in a table with another column showing the account name then I get a health score for each account, EG:

AccountIDHealth Score
abc20
def7
ghi-55
jkl2
mno1

What I'd like to do is count how many accounts have health scores in different bands. Eg to show a pie chart that shows how many accounts have a score below zero, how many have a score between 0 and 75, and how many are 76 and over.

 

Is it possible to do such a calculation based on values in a measure?  I'm stuck and any help is greatfully received!

 

Thank you 🙂

 

 

1 ACCEPTED SOLUTION

You should be able to do it like this:

 

Table = SUMMARIZE('TABLE','TABLE'[Column],"MyMeasure",[Measure])

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

15 REPLIES 15
Greg_Deckler
Super User IV
Super User IV

So, you should be able to create a table using SUMMARIZE that includes this measure and do your grouping that way. Or create another table with your categories and do a count in that table that fit the defined criteria.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Greg_Deckler,

 

Thank you for such a quick reply 🙂 

 

When I try with SUMMARIZE I don't have an option to do it on the basis of a Measure - it looks like it is only available for table columns.

 

 

You should be able to do it like this:

 

Table = SUMMARIZE('TABLE','TABLE'[Column],"MyMeasure",[Measure])

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Thanks Derek. I had similar issue and your response was helpful. 

Cheers - sharan 

 

Thank you so much, that's brilliant 🙂

 

When i'm creating the table, is it possible to add a filter inso that it only calculates the score where, say, the Visits column is > 5?

 

 

Sure can:

 

Table = FILTER(SUMMARIZE('TABLE','TABLE'[Column],"MyMeasure",[Measure]),<filter expression>)

Is "Visits" your equivalent of "MyMeasure"?

 

If so, then it would be:

 

Table = FILTER(SUMMARIZE('TABLE','TABLE'[Column],"MyMeasure",[Measure]),[MyMeasure]=5)

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Greg_Deckler

 

I have the same issue only difference is my filter will be selected by user from a slicer. How to do this now ?

Well, there you are going to run into a problem. Tables are not dynamic based upon user input, they get calculated at the time of query refresh. I will provide the how, if it was not a table, but a measure below but perhaps we need to backup and truly understand your data and what you are trying to accomplish.

 

Measure = 
VAR __SelectedValue = MAX('SlicerTable'[SlicerColumn]

VAR __tmpTable = 
FILTER(SUMMARIZE('TABLE','TABLE'[Column],"MyMeasure",[Measure]),[MyMeasure]=__SelectedValue)

...<something>

RETURN

<something>

So basically, you are grabbing the selected value from the slicer and using it in your filter clause. But, you have to do some additional calculations/manipulation to return a single value from a measure.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hey Greg!

I am having this exact same issue.. Would you be willing to assist me with this?

 

Thank you!

Hi @Greg_Deckler

 

Thanks for the reply

 

I have posted my query here

https://community.powerbi.com/t5/Desktop/Dynamic-Grouping-on-Sum/m-p/454924#M210785

 

I will try your measure and will update here.

OK, based on the data in the other post, I created a Category table with:

 

Category

0-50 percent
51-75 percent
75-100 percent

 

And a measure like this:

 

Measure = 
VAR __Date = MAX('Table'[date])
VAR __Category = MAX('Categories'[Category])
VAR __Low = 
SWITCH(
    __Category,
    "0-50 percent",0,
    "51-75 percent",.51,
    "75-100 percent",.75
)
VAR __High = 
SWITCH(
    __Category,
    "0-50 percent",.5,
    "51-75 percent",.74,
    "75-100 percent",1
)
VAR __tmpTable = SUMMARIZE('Table','Table'[userid],"__Percent",MAX('Table'[profilepercent]))
RETURN COUNTROWS(FILTER(__tmpTable,[__Percent]>=__Low && [__Percent]<=__High))

PBIX is attached.

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Greg_Deckler 

 

Can you please guide me on my problem statement, i have posted on community. Please use below link:
https://community.powerbi.com/t5/Desktop/Partition-Measure-values-into-groups-and-change-dynamically...

Thanks

Sanchit

@Greg_Deckler 

This solution is fantastic.

 

I have a question though.  This will allow incoming filters to change the output, but since it's the result of a temp table calculation what do I need to do to allow filters to be passed back out of a widget that is using the grouped up values?  If I click on one of the groups in the widget using this formula, nothing else on the page gets filtered by the selection.  Is there somehow a way to define a relationship back out, say...using the ID(s) that are contained in the selected group?

 

Attached image: Red box is using the grouping formula but the yellow boxed widgets are not being filtered by the selection in the red box.  All widgets share a common unique identifier of PropertyID, which is included in the summarized data in the formula but cannot be filtered back out.

image.png

 

 

I have a similar issue...

 

A table with 2 columns: ID, Date

I calculated the nr of days between a date that is filtered by the user and the date of every ID included in my Table.

The measure returns a correct value:

NrDays= if(HASONEVALUE(Calendar[Date]); DATEDIFF(MAX('Table'[Date]);MAX('Calendar'[Date]);DAY);0)

 

I created a table with this grouping:

0-100

100-500

500-1000

 

 

I want to count how many id I have in the first group, the second and the third based by my measure NrDays ( for example if I have 3 ID with Nr Days=21,30 and 80 I want to count 3 in the first range 0-100)

How can I achieve this?

Hi @Greg_Deckler

You rock man!!!

 

It worked after small tweak. And you have given me a whole new dimension to figure new stuff i can do with this.

 

The actual query which worked for me


User Count Profile Percentage =
VAR __Category = MAX('Category'[Category])
VAR __Low =
SWITCH(
    __Category,
    "0-50 percent",0,
    "51-75 percent",51,
    "75-100 percent",75
)
VAR __High =
SWITCH(
    __Category,
    "0-50 percent",50,
    "51-75 percent",74,
    "75-100 percent",100
)
//VAR __tmpTable = SUMMARIZE('Table','Table'[userid],"__Percent",SUM('Table'[profilepercent]))
//RETURN COUNTROWS(FILTER(__tmpTable,[__Percent]>=__Low && [__Percent]<=__High))

var _Temp_tbl = SUMMARIZE('Periodic %age completion update','Periodic %age completion update'[UserId],"_sumPer", CALCULATE(SUM('Periodic %age completion update'[ProfilePercentage]),FILTER('Periodic %age completion update','Periodic %age completion update'[UpdatedDate]<='Date Dim'[Date selected] && 'Periodic %age completion update'[UpdatedDate]>= [Least Date])))

return
COUNTROWS(FILTER(_Temp_tbl,[_sumPer] >= __Low && [_sumPer] <= __High))

 

Thanks !!!!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors