Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KellyLen
Helper III
Helper III

New measure

Hi,

 

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

image.png

 

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 

1 ACCEPTED SOLUTION

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

 

per.png

 

 

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] ) )
            )
        )
    )
)

percent.png

 

Total calculations can also be redone to not include NOK

 

Regards,

MFelix

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

20 REPLIES 20
v-yulgu-msft
Employee
Employee

Hi @KellyLen,

 

Suppose your table structure is like below:

1.PNG

 

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] )
    )
)

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:
image.png

 

 

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. 

 

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

 

per.png

 

 

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] ) )
            )
        )
    )
)

percent.png

 

Total calculations can also be redone to not include NOK

 

Regards,

MFelix

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi,

 

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

image.png

 

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

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you so much! It really worked! 🙂 

 

Kelly 

Hi again,

 

How to make an easier calculation, where I should only calculate percentage of OK/Grand total? 

 

Best regards,

Kelly 

Hi @KellyLen,

 

Not sure of what you want to calculate can you give an example please.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



When I have the table only like this:

image.png

 

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

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix Thank you, the previous formula worked perfectly as usually. 🙂 

 

I have query with following columns:

image.png

 

And have created tabels like these:

image.png

 

 

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 

 

 

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

 

Best regards,

Kelly 

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

 

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@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 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

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

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.