I have a table that looks something like this:
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:
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 🙂
Solved! Go to Solution.
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.
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.
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?
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)
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.
Thanks for the reply
I have posted my query here
I will try your measure and will update here.
OK, based on the data in the other post, I created a Category table with:
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.
Can you please guide me on my problem statement, i have posted on community. Please use below link:
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.
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:
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?
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 =
VAR __High =
//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])))
COUNTROWS(FILTER(_Temp_tbl,[_sumPer] >= __Low && [_sumPer] <= __High))
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates