Percent of column total

[Giovani]

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?

[v-ljerr-msft · Accepted solution]

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]

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

That code solved my issue.

[Ashish_Mathur]

Hi,

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