Forum Discussion

IPGeorgiev's avatar
IPGeorgiev
Helper III
6 years ago

Calculating impact within a Matrix

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

Need Help

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    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's avatar
      IPGeorgiev
      Helper III
      6 years ago

      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's avatar
        MFelix
        Super User
        6 years ago

        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.