Forum Discussion

Sureshot's avatar
Sureshot
Helper I
8 years ago
Solved

DAX expression I cannot get to work - please help.

                      Hi. My first post. So I have a matrix and on this matrix I am trying to calculate in DAX a field called % of Premium. Please see pic above, for 2017 the % of...
Need Help
  • Sureshot's avatar
    Sureshot
    8 years ago

    Here's what worked but I don't understand why!

    I just added the red part and all worked.

     

    % of Premium = DIVIDE(SUM('Agency Scorecard 3'[Earned Premium]), CALCULATE(SUM('Agency Scorecard 3'[Earned Premium]), ALLEXCEPT('Agency Scorecard 3','Agency Scorecard 3'[Loss Year]),'Agency Scorecard 3'[Summary Line ID],'Business Division'[BusinessDivisionID], VALUES('Business Division'[BusinessDivisionID])), 0)

     

    Thanks all for your help in getting me on the right track.

     

    Here's the post that explains why the Values function works so now I know.

    https://community.powerbi.com/t5/Desktop/ALLEXCEPT-only-working-on-visible-row-context/td-p/18030

     

    You keep adding for all your report (might work with page/visualisation filters I don;t know) filters as follows:

     

    % of Premium = DIVIDE(SUM('Agency Scorecard 3'[Earned Premium]), CALCULATE( SUM('Agency Scorecard 3'[Earned Premium]), ALLEXCEPT('Agency Scorecard 3','Agency Scorecard 3'[Loss Year]),'Agency Scorecard 3'[Summary Line ID],'Business Division'[BusinessDivisionID], VALUES('Business Division'[BusinessDivisionID]), VALUES('Agent'[Agent Alternate 1]), VALUES('Agent'[Agent Alternate 2]), VALUES('Sub Producer'[Alt Sub Producer 2]), VALUES('Managing Unit'[Managing Unit Name]), VALUES(Office[Office]), VALUES('Product Line'[Product Line Name]), VALUES(Underwriter[Underwriter Name]) ),0)

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hi Sureshot

 

Instead of ALLSELECTED()

 

could you try

 

Allexcept(TableName,TableName[LossYear])

Sureshot's avatar
Sureshot
Helper I
8 years ago

Hi. Thanks but that gave 7.28% for the example I gave. I donlt even know how 7.28% is caculated.

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi Sureshot

     

    Actually ALLSELECTED works on Totals. It does not impact Row and Column filters

     

    Could you share your file via googledrive or onedrive?

    • Sureshot's avatar
      Sureshot
      Helper I
      8 years ago

      I don;t yet have either of thse accounts but I can look into setting one up and sending my pbix file.

      The thing is here I am looking to get row totals. AllSelected works great for columns but now row columns i.e. I need to get the 793,274,434 figure as the divisor.

      • tjd's avatar
        tjd
        Impactful Individual
        8 years ago

        Have you tried using:

        % of Premium = SUM('Agency Scorecard 3'[Earned Premium]) / CALCULATE(SUM('Agency Scorecard 3'[Earned Premium]), ALLSELECTED('Agency Scorecard 3'[Earned Premium]))?

         

        For the row you've selected, you need to tell it what should remain unfiltered.  Your expression (ALLSELECTED()) is telling it to unfilter everything which results in the % of total, not row.