Forum Discussion

Helper 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

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

v-yulgu-msft

Microsoft 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

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

MFelix

Super 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
Helper 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
  Super User
  8 years ago
  I just made a change ot my answer please check it.

Forum Discussion

New measure

Related content

Measure

DAX Measure

Path Measure

% of total measure

Count Measure