Calculating percentage between two numbers with ability to capture abnormalities

[Justas4478]

Hi, I am trying to create a 'Fullfilment Rate' measure that calculates percentages between two values and show is as a %.
Ideally maximum % should be 100% but if it brings back more than 100% it should display something like 'Over Demand' or something that would make sense as and error message.
This is example screenshot.

Thanks.

[zenisekd]

 try:
=
IF (
    DISTINCTCOUNT ( table[DocumentNumber] ) = 1,
    IF (
        DIVIDE ( [TotalActual] , [TotalDemand]  ) > 1,
        "Over Demand",
        FORMAT (
            DIVIDE ( [TotalActual] , [TotalDemand] ),
            "Percent"
        )
    )
)

Kudos and mark as solution appreciated.

[Justas4478]

@zenisekdHi, unfortunately it does not work since my Total Actual and Demand are measures and not columns.
Sorry for not including in initial request message.

[zenisekd]

I adjusted it to work with measures.

[Justas4478]

@zenisekd It is working, but for some reason it creates additional rows.

[zenisekd]

Ok, try just    

= IF (
        DIVIDE ( [TotalActual] , [TotalDemand]  ) > 1,
        "Over Demand",
        FORMAT (
            DIVIDE ( [TotalActual] , [TotalDemand] ),
            "Percent"
        )
    )

[Justas4478]

@zenisekd Still same, the only change was that 'Over Demand' now is showin in Total line.

[zenisekd]

Yeah, that is what was the first part for... in that case stick to the previous:
=
IF (
    DISTINCTCOUNT ( table[DocumentNumber] ) = 1,
    IF (
        DIVIDE ( [TotalActual] , [TotalDemand]  ) > 1,
        "Over Demand",
        FORMAT (
            DIVIDE ( [TotalActual] , [TotalDemand] ),
            "Percent"
        )
    )
)

And apply a filter on top of the visual saying that Fulfilment Rate must not be blank/empty...

[Justas4478]

@zenisekd I dont know is this helpfull but DocumentNumber sits in 'Outbound Delivery Documents' table where Total Actual and Demand sits in 'Outbound Delivery' table.
Maybe that is causing additional lines?

[Justas4478]

@zenisekd What I noticed is that measure is trying to calculate for each 'ItemNumber' for each 'DocumentNumber' even when there is no Actual or Demand data in them.

[zenisekd]

As I said previously, just filter the blanks out on the visual. I don't have the sample data to play with it so, I consider this the best solution. 

[Justas4478]

@zenisekd I just tried to add is not blank filter like you suggested, but it did not work for some reason.
It does not allow me to enter any text only asking for numbers.

[zenisekd]

use "is not empty" instead.

[Justas4478]

@zenisekd I don't get is not emply as available chaices on the list.

[zenisekd]

I tried to replicate this on my sample data and it is there. 

...

[Justas4478 · Accepted solution]

@zenisekd I created this measure: 

DIVIDE('Outbound Delivery'[Total Actual],'Outbound Delivery'[Total Demand],0) * 1
It seasm to be working and it got rid of additional rows.
The lines might have something to do with the formating function.

[zenisekd]

I am confused here, previously you said, that it is two measures and that you want the text there, if it is over 1...?

[Justas4478]

@zenisekd Yes, but as you seen trying to add text for some reason creates addition rows.
And for some reason removing blanks does not remove them and I don't get choice to remove empty.

[Justas4478]

The 'Total Actual' and 'Total Demand' are a measures and not a column.

I cant create calculated columns since I am using company data model that is Live connected.