Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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: "P...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

     

kamalmsharma's avatar
kamalmsharma
Helper II
6 years ago

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

 

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!