Grouping values from measures

[RichardP]

Hi,

I have a table that looks something like this:

AccountID	UserID	Visits	Help requests
abc	111	5	0
abc	456	0	1
abc	789	15	2
def	555	2	2
def	666	0	1

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:

AccountID	Health Score
abc	20
def	7
ghi	-55
jkl	2
mno	1

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 🙂

[Greg_Deckler]

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.

[RichardP]

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.

[Greg_Deckler · Accepted solution]

You should be able to do it like this:

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

[Anonymous]

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

Cheers - sharan 

[RichardP]

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?

[Greg_Deckler]

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)

[kohlivinayak]

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 ?

[Greg_Deckler]

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.

[prill2ta]

Hey Greg!

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

Thank you!

[kohlivinayak]

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.

[Greg_Deckler]

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.

[TaariqHussain]

@Greg_Deckler 

Hope you well

I tried using the above formula,  please see below, need some assistance or maybe an alternative

I have 2 tables items sales and a second that has cost

both linked to master inventory

I have a measure that calculates my GP% however i want to group my GP's as per below

thats where your formula came in

Measure =

VAR __Date = MAX('ItemWiseSales'[dateinvoiced])

VAR __Category = MAX('Category'[Category])

VAR __Low =

SWITCH(

    __Category,

    "Less than 1%",-0.2,

    "1%",0.01,

    "2%",0.02,

    "3-5%",0.03,

    "5%>",0.05

)

VAR __High =

SWITCH(

    __Category,

"Less than 1%",0.01,

    "1%",0.019,

    "2%",0.029,

    "3-5%",0.049,

    "5%>",0.2

)

VAR __tmpTable = SUMMARIZE('ItemWiseSales',ItemWiseSales[itemprice],"__Percent",[GP%])

RETURN COUNTROWS(FILTER(__tmpTable,[__Percent]>=__Low && [__Percent]<=__High))

i than pull that measure into a pie chart but the visual is completely off

[sanchit_1610]

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

[ctaylor]

@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.

[Anto69]

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?

[kohlivinayak]

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