Struggling with grouping

[kman42]

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-msft · Accepted solution]

hi, @kman42 

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

[kman42]

Thanks. It looks like that will work, but could you explain what the measure is doing?

Thanks!

[v-lili6-msft]

hi, @kman42 

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/63376

Also, 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/547907

Best Regards,

Lin

[kamalmsharma]

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

www.addendanalytics.com

---

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

---

[kman42]

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]

@kman42 ,

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.