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 ca...
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

     

     

     

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. 

 

MFelix's avatar
MFelix
Icon for Super User rankSuper User
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

 

 

 

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

    Hi,

     

    Actually, I would need the total calculation be based on statuses totals as well.
    The hole visual looks actually like this:

     

    I do not know, if the subcategory there changes anything. I did not noticed to add this at first. :/

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      8 years ago

      I just made a change ot my answer please check it.

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

        Thank you so much! It really worked! :) 

         

        Kelly