Price per Quantity where Quantity equals zero

DAX DAX Calculations
1

Hi All,

I am required to create measures to convert revenue and cost to a $/Qty.

My issue is that some Credits do not have a quantity. This is my formula for Net Price per Tonne:

Net Price per Tonne =
AVERAGEX(Sales,
IFERROR([Net Revenue]/[Total Tonnes],BLANK())
)

This is my result:

image
image793×534 21.6 KB

At the lowest level it’s doing what i’d like, but at the total customer level it’s not. At a customer level it’s not taking the total value of the credits and dividing it by the total tonnes (the reason for some zero quantities is quality credits, so no qty returned, the credits only effect the price).

Any ideas how i can get around this? Do i need to base my measures on a single context or should i be able to apply them to multiple contexts?
Thanks,
Bronwyn

2

@bgood,

You’ve correctly diagnosed the problem as one of insufficient context at the total line. Here’s one way to handle it (note that I’ve guessed at some of your table names so you may need to tweak this a bit to get it to work in your report). The logic applied (using IF(HASONEVALUE()) is if there is a single customer description, use your AVERAGEX measure as is, if not (i.e., the total line), then divide SUMX of net revenue by SUMX of total tonnes.

Net Price per Tonne =

VAR CustAvg =
    AVERAGEX (
        Sales,
        IFERROR (
            [Net Revenue] / [Total Tonnes],
            BLANK ()
        )
    )
VAR TotRev =
    SUMX (
        Sales,
        [Net Revenue]
    )
VAR TotTonnes =
    SUMX (
        Sales,
        [Total Tonnes]
    )
RETURN
    IF (
        HASONEVALUE ( 'Customer'[CustomerDescription] ),
        CustAvg,
        DIVIDE (
            TotRevenue,
            TotTonnes,
            0
        )
    )

I hope this is helpful.

  • Brian
2 Likes
3

Hi @bgood, we’ve noticed that no response has been received from you since 26th February 2020. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

4

Hi @BrianJ, thanks for the response. The solution you have provided gives me the same outcome as my original formula.

image

5

Hi @bgood,

Just helping you to understand solution given by @BrianJ.

It all depends on context. Previously, you were trying to use ‘Customer’[CustomerDescription] column and Brian’s formula would work on that. Now, you are using DocumentNo column in your table. You will have to update below code if you want to use other column to get average.

HASONEVALUE ( ‘Customer’[CustomerDescription] )

Please let me know if some thing is not clear.

1 Like
6

@hafizsultan thank you. My apologies, the screenshot i took was not quite like for like. My comment stands, as even at the Customer Description context, the total of all credits & invoices is not being calculated correctly. Price/t being Brian’s formula.

image
image828×220 7.03 KB

7

Hi @bgood,

Thanks for swift response. Can you please try below:

Net Price per Tonne =
VAR CustAvg =
    AVERAGEX ( Sales, IFERROR ( [Net Revenue] / [Total Tonnes], BLANK () ) )
RETURN
    IF (
        HASONEVALUE ( 'Customer'[CustomerDescription] ),
        CustAvg,
        DIVIDE ( [Net Revenue], [Total Tonnes], 0 )
    )
1 Like
8

That worked @hafizsultan.
Excellent, thank you very much :slight_smile: :sweat_smile:

1 Like
9

@bgood, Glad that it worked for you :slight_smile: