cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Grouping values from measures

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 🙂

1 ACCEPTED SOLUTION
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
16 REPLIES 16
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper I

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.

Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Cheers - sharan

Helper I

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?

Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Resolver I

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

Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hey Greg!

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

Thank you!

Resolver I

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.

Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper I

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

Helper I

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

Helper III

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

Regular Visitor

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?

Resolver I

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors