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

When I have the table only like this:

 

And I would like to calculate the percentage: OK / Total.

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

Make and adaptation of the previous formula:

 

percentage = 

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

 

Regards,

MFelix

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

    MFelix Thank you, the previous formula worked perfectly as usually. :) 

     

    I have query with following columns:

     

    And have created tabels like these:

     

     

    I would like to create measure, which would calculate the percentage  1st pass matching / Matching success. 

     

    Are you able to help me.. again? :) 

     

    Best regards,

    Kelly 

     

     

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

    MFelix Sorry for bothering, but are you able to help me?

     

    Best regards,

    Kelly 

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

    Hi KellyLen,

     

    what is the type of data you have in the 1st pass match?

     

    You want to calculate the % based on the total value of that column?

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

    Hi MFelix,

     

    I have there values: Count of 1st pass match. Rows are Order origin. and columns are Date. 

     

    I would like to calculate % based on Matching success values. For example based on my previous screenshots I would like to calculate Fuel TBO % - 4/68 = 0,0588 .. and like this for each row. 

     

     

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

    HI KellyLen,

     

    You need to do something like this:

     

    1st match =
DIVIDE (
    COUNT ( 'Matching Success'[1st pass matching] ),
    COUNT ( 'Matching Success'[Order Origin] )
)

    Regards,

    MFelix

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

    MFelix Thanks, it basically works. But I would like exclude from Matching success values with No PO. Is it possible to add it to the formula somehow? 

     

    Best regards,

    Kelly 

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

    How do you get the information that it doesn't ave PO?

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

    I have in order origin four values No PO, PO, Crew hotel TBO and Fuel TBO. I would like to exclude the No PO values. 

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

    Just filtering works as well. :) Thank you for helping me again. :)