Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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
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
@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!
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?
@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
Proud to be a Super User!
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |