Forum Discussion

KellyLen's avatar
KellyLen
Icon for Helper III rankHelper III
8 years ago
Solved

New measure

Hi,

 

How should the DAX look like if I would like to create measure with following information:

 

Used columns are:

  • OK/NOK
  • Status

And values are Amount in EUR. 

 

I would like to calculate the percentage, where the formula is like Status: 1. Execution / Overall Total. For example 28 250 083,14 / 36 057 104,09 = ... 

 

Could you please help me with this?

 

Best regards,

Kelly 

Need Help
Tips and Tricks
  • MFelix's avatar
    MFelix
    8 years ago

    Hi KellyLen,

     

    Based on your table try to use this measure:

    percentage = 

    IF (
        DISTINCTCOUNT ( 'Status'[Status] ) > 1 ,
        1 ,
        IF (
            DIVIDE (
                CALCULATE (
                    SUM ( 'Status'[Amount in EUR] ),
                    'Status'[Status] = VALUES ( 'Status'[Status] )
                ),
                CALCULATE ( SUM ( 'Status'[Amount in EUR] ), ALL ( 'Status'[Status],'Status'[NOK/OK] ) )
            )
                = BLANK (),
            0 ,
            DIVIDE (
                CALCULATE (
                    SUM ( 'Status'[Amount in EUR] ),
                    'Status'[Status] = VALUES ( 'Status'[Status] )
                ),
                CALCULATE ( SUM ( 'Status'[Amount in EUR] ), ALL ( 'Status'[Status],'Status'[NOK/OK] ) )
            )
        )
    )

    Then format as % and you should get the result below (put it as table to be more visible).

     

     

     

    If you need to take out only the Ok status and remove the NOK from the calculation

     

    percentage =
IF (
    MAX ( 'Status'[NOK/OK] ) = "NOK",
    BLANK (),
    IF (
        DISTINCTCOUNT ( 'Status'[Status] ) > 1,
        1,
        IF (
            DIVIDE (
                CALCULATE (
                    SUM ( 'Status'[Amount in EUR] ),
                    'Status'[Status] = VALUES ( 'Status'[Status] )
                ),
                CALCULATE ( SUM ( 'Status'[Amount in EUR] ), ALL ( 'Status'[Status] ) )
            )
                = BLANK (),
            0,
            DIVIDE (
                CALCULATE (
                    SUM ( 'Status'[Amount in EUR] ),
                    'Status'[Status] = VALUES ( 'Status'[Status] )
                ),
                CALCULATE ( SUM ( 'Status'[Amount in EUR] ), ALL ( 'Status'[Status] ) )
            )
        )
    )
)

     

    Total calculations can also be redone to not include NOK

     

    Regards,

    MFelix

     

     

     

20 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi KellyLen,

     

    Suppose your table structure is like below:

     

    Create a measure and add it into Matrix visual.

    Percentage =
DIVIDE (
    MAX ( 'New Measure'[Amount] ),
    CALCULATE (
        SUM ( 'New Measure'[Amount] ),
        ALLEXCEPT ( 'New Measure', 'New Measure'[Category] )
    )
)

     

    Best regards,

    Yuliana Gu

    • KellyLen's avatar
      KellyLen
      Icon for Helper III rankHelper III
      8 years ago

      Hi,

       

      Actually all the types that have value are OK and rows that do not have type are NOK.  I have not got currency in different column, I just have the amounts already in EUR. So the table is like this:

       

       

      The result you got, seems to be the one I would like to get, but I do not understand the formula you created. And the result definitely has to be in percentage already. 

       

      • KellyLen's avatar
        KellyLen
        Icon for Helper III rankHelper III
        8 years ago

        Is there anyone that could help me? I would like to know more about creating measures, but I do not understand the logic of it.