Forum Discussion
Struggling with grouping
I have a table that lists status history line items. It has columns called Status and Created On as well as a key field to relate it to another table. Status contains a unique value from the list: "Pending", "Awarded", "Not Awarded", "Abandoned". There are a few others, but that is enough for the example. I set up a Matrix visual and added Status to the column list and a simple count to values. This works fine, but I don't want to list all of the statuses. Instead, I want a column for "Awarded" and one for "Sent" that is a group of "Pending", "Awarded" and "Not Awarded", but excludes "Abandoned". I tried creating a group, but I don't see a way to have "Awarded" part of the "Sent" group and also on it's own. Eventually, I want a measure Success Rate = count(status="Awarded")/count(status="Sent")*100 [psuedocode, obviously].
Any help?
Thanks!
hi, Anonymous
You could try this way that create a new manually entered table and keep the cross filter direction is "Both"
And the total for it will give only first Sent.
For example:
so you need to use the measure as below:
Measure = var _table=ADDCOLUMNS('group',"_value",CALCULATE(SUM(Basic[Value]))) return SUMX(_table,[_value])here is sample pbix file, please try it.
Best Regards,
Lin
7 Replies
Would it be possible to do this by creating a new manually entered table as follows:
Category Status Awarded Awarded Sent Awarded Sent Pending Sent Not Awarded Abandoned Abandoned
And then using the Category column as a column on the matrix visual and filtering for just Awarded and Sent? That way for the Awarded category it would just pull in the Awarded status, but for the Sent category it would pull in everything except for the Abandoned status?
- Nathaniel_C
Community Champion
Anonymous ,
No it will try to filter and give only first Sent.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel- Nathaniel_C
https://community.powerbi.com/t5/DAX-Commands-and-Tips/DATATABLE-code-help/m-p/801126#M4849
You might look at this link to a question on datatable.
- kamalmsharma
Helper II
Hi,
You can create a calculated column using if function which assigns two values "Awarded" and "Sent" for corresponding values in status column. Then bring that new column into the matrix visual.
Hope this helps. Let me know in case you need help in wrinting functions.
Regards,
kamal
Anonymous wrote:I have a table that lists status history line items. It has columns called Status and Created On as well as a key field to relate it to another table. Status contains a unique value from the list: "Pending", "Awarded", "Not Awarded", "Abandoned". There are a few others, but that is enough for the example. I set up a Matrix visual and added Status to the column list and a simple count to values. This works fine, but I don't want to list all of the statuses. Instead, I want a column for "Awarded" and one for "Sent" that is a group of "Pending", "Awarded" and "Not Awarded", but excludes "Abandoned". I tried creating a group, but I don't see a way to have "Awarded" part of the "Sent" group and also on it's own. Eventually, I want a measure Success Rate = count(status="Awarded")/count(status="Sent")*100 [psuedocode, obviously].
Any help?
Thanks!
v-lili6-msftCommunity Support
hi, Anonymous
You could try this way that create a new manually entered table and keep the cross filter direction is "Both"
And the total for it will give only first Sent.
For example:
so you need to use the measure as below:
Measure = var _table=ADDCOLUMNS('group',"_value",CALCULATE(SUM(Basic[Value]))) return SUMX(_table,[_value])here is sample pbix file, please try it.
Best Regards,
Lin
- v-lili6-msft
hi, Anonymous
It likes a measure totals problem. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376Also, this Quick Measure, Measure Totals, The Final Word should help you:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Best Regards,
Lin
