cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Giovani Regular Visitor
Regular Visitor

Percent of column total

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?

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Percent of column total

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" ) )
        )
    )

c2.PNG

 

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

 

Regards

3 REPLIES 3
Super User
Super User

Re: Percent of column total

Hi,

 

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

v-ljerr-msft Super Contributor
Super Contributor

Re: Percent of column total

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" ) )
        )
    )

c2.PNG

 

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

 

Regards

Giovani Regular Visitor
Regular Visitor

Re: Percent of column total

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

That code solved my issue.