cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

View solution in original post

16 REPLIES 16
Greg_Deckler
Super User
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...

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

 

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

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

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

@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

TaariqHussain_0-1681913449108.png

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
TaariqHussain_1-1681913629729.png

 

 

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 November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Dashboard in a day with date

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!

Power BI Fabric Summit Carousel

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