Calculating impact within a Matrix

[IPGeorgiev]

Dear All,

I want to replicate something that I have at the moment in Excel in Power BI.

Let me just first show the formula in excel:

So once I have a MATRIX as the one on the picture (without the Impact Column) I want to add this Impact column.

Here some input about the data in the matrix:

The Names are filtered based on time period and based on location.

Positive% = Positive/Total Responses

Postive = Sum for this Name (based on the filters applied)

I have tried several times with Calculate and Divide however I cant get this results 😞 

Is this even possible to be accomplished?

Many thanks in advance!

Best regards,

Ivan

[MFelix]

Hi @IPGeorgiev ,

Believe that the positive % is calculated in the wrong way since the percentages calculated are based on the negative and not the positive column, so you must have calculated incorrectly or the columns are incorrectly named.

I don't know how your model is setup but I assume you have a postive and a negative column so add the following measures:

Total responses = SUM('Table'[Positive])+SUM('Table'[Negative])

Negative % = DIVIDE(SUM('Table'[Negative]) ; [Total responses])

Impact =
CALCULATE ( [Positive %]; ALL ( 'Table' ) )
    - (
        CALCULATE ( SUM ( 'Table'[Negative] ); ALL ( 'Table' ) )
            - SUM ( 'Table'[Negative] )
    )
        / ( CALCULATE ( [Total responses]; ALL ( 'Table' ) ) - [Total responses] )

As I refer believe you naming or calculations are incorrect so I named my measure negative % if you want to calculate over the postive just change the column.

Check PBIX file attach.

If your data is on a different setup please tell me so that measures can be adjusted.

[IPGeorgiev]

Hi @MFelix ,

Many many thanks for the time and the response!!

As for the calcualtion - I used dummy data since the real one is confidential so I have swapped Postive and Negative in the screenshot. However this is not the important in this case as, as you said, this can easily be fixed.

When I apply this to my file I get this:

Before:

After:

And + there are other team members added to the list, which shouldnt be there (since this matrix has additional filters applied , using slicers - related to Location, Type of transactions, TIme Period) Using all disables them in some way as under Agent i get all the names (like there are no filters) ,however those agents, who dont meet the already applied filters have empty values under NO, Yes etc.

So I guess this makes it much more compliacated 😞

[MFelix]

Hi @IPGeorgiev ,

Measures are based in context so each value, filter, slicer, that you add to the calculation changes the outcome.

One question is the calculation for the impact column correct?

If yes you just need to redo the measure to something similar to this:

Impact =
IF(SUM(Table[Negative])= 0 ; BLANK();


CALCULATE ( [Positive %]; ALL ( 'Table' ) )
    - (
        CALCULATE ( SUM ( 'Table'[Negative] ); ALL ( 'Table' ) )
            - SUM ( 'Table'[Negative] )
    )
        / ( CALCULATE ( [Total responses]; ALL ( 'Table' ) ) - [Total responses] ))

If this does not work I need to have some more context so I can point on the rigth diretion.

[IPGeorgiev]

Hi @MFelix ,

I see you have added index column - is this neeeded for this to work as I dont have it?

With the new solution, when I implement it  one of the issues is solved - I no longer get the agent names for other locations (so it respects the applied filter so to say). However now if I have an agent who has 1 Negative and 0 Positive responses I get blank field for impact.

For the rest for Impact the values are either 0.00% or -0.00% - so there is still something wrong - even if I remove all the filters it still doesnt work. I have double checked and I have applied it exactly the way you have (same formulas)

I will be glad to provide more information if needed.

Many thanks!

Best regards,

Ivan

[MFelix]

Hi @IPGeorgiev ,

The index was just for sorting purposes nothing more, other wise would be name 1, name 10 ... instead if name 1, name 2..

Again, without any information it's difficult if you can give a mockup of your data and the setup I can give you a better answer.

You can send the file by private message if it's possible.