- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Percent of column total

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Giovani

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2017
02:18 PM

I am relatively new in PowerBI and I am trying to solve an issue based on the data below:

ID | Result | Date |

1 | Negative | 1/1/17 |

1 | Inconclusive | 1/1/17 |

1 | Positive | 1/1/17 |

1 | Suspect | 1/1/17 |

1 | Positive | 1/1/17 |

2 | Positive | 2/2/17 |

2 | Positive | 2/2/17 |

2 | Negative | 2/2/17 |

2 | Negative | 2/2/17 |

3 | Negative | 4/2/17 |

3 | Suspect | 4/2/17 |

3 | Negative | 4/2/17 |

4 | Positive | 4/3/17 |

4 | Positive | 4/3/17 |

4 | Positive | 4/3/17 |

5 | Negative | 6/17/17 |

5 | Negative | 6/17/17 |

5 | Negative | 6/17/17 |

5 | Suspect | 6/17/17 |

5 | Negative | 6/17/17 |

I am trying to build percentage results based on the ID column considering that:

1- if for one ID has one result positive that will consider the ID like positive and do not count for the other results. If there are no positive case and one suspect result count as

2 - If there is no positive case, but one suspect result counts as suspect and do not count the others.

3- If there are no positive or suspect, but Inconclusive should count as Inconclusive.

4 - If only negative is related with the ID count as negative.

When I try to calculate the results are counted more than once for each ID. And at the end, I have more results than the total of ID`s.

Besides of that, I want to build a graph taking the timeline in the x-axis.

Any thoughts how can I solve this?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

v-ljerr-msft

Super Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-29-2017
12:54 AM

Hi @Giovani,

If I understand you correctly, you can use the formula below to create a new calculate **column** in your table to get the actual Result for each ID based on considerations mentioned above.

Actual Result = VAR positives = CALCULATE ( COUNTA ( Table1[Result] ), FILTER ( ALLEXCEPT ( Table1, Table1[ID] ), Table1[Result] = "Positive" ) ) VAR suspects = CALCULATE ( COUNTA ( Table1[Result] ), FILTER ( ALLEXCEPT ( Table1, Table1[ID] ), Table1[Result] = "Suspect" ) ) VAR inconclusives = CALCULATE ( COUNTA ( Table1[Result] ), FILTER ( ALLEXCEPT ( Table1, Table1[ID] ), Table1[Result] = "Inconclusive" ) ) VAR negatives = CALCULATE ( COUNTA ( Table1[Result] ), FILTER ( ALLEXCEPT ( Table1, Table1[ID] ), Table1[Result] = "Negative" ) ) RETURN IF ( positives >= 1, "Positive", IF ( suspects >= 1, "Suspect", IF ( inconclusives >= 1, "Inconclusive", IF ( negatives >= 1, "Negative" ) ) ) )

Then you should be able to use the "Actual Result" column to easily get the expected result in your scenario.

Regards

3 REPLIES 3

Ashish_Mathur

Super User

Re: Percent of column total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2017
06:44 PM

Hi,

What final results are you expecting? Show those numbers and method of arriving at those numbers.

v-ljerr-msft

Super Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-29-2017
12:54 AM

Hi @Giovani,

If I understand you correctly, you can use the formula below to create a new calculate **column** in your table to get the actual Result for each ID based on considerations mentioned above.

Actual Result = VAR positives = CALCULATE ( COUNTA ( Table1[Result] ), FILTER ( ALLEXCEPT ( Table1, Table1[ID] ), Table1[Result] = "Positive" ) ) VAR suspects = CALCULATE ( COUNTA ( Table1[Result] ), FILTER ( ALLEXCEPT ( Table1, Table1[ID] ), Table1[Result] = "Suspect" ) ) VAR inconclusives = CALCULATE ( COUNTA ( Table1[Result] ), FILTER ( ALLEXCEPT ( Table1, Table1[ID] ), Table1[Result] = "Inconclusive" ) ) VAR negatives = CALCULATE ( COUNTA ( Table1[Result] ), FILTER ( ALLEXCEPT ( Table1, Table1[ID] ), Table1[Result] = "Negative" ) ) RETURN IF ( positives >= 1, "Positive", IF ( suspects >= 1, "Suspect", IF ( inconclusives >= 1, "Inconclusive", IF ( negatives >= 1, "Negative" ) ) ) )

Then you should be able to use the "Actual Result" column to easily get the expected result in your scenario.

Regards

Giovani

Regular Visitor

Re: Percent of column total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-29-2017
09:13 AM

Thanks a lot, v-ljerr-msft !!!!!!!!!!!

That code solved my issue.