Puzzling measure behaviour, yet simple (?) problem

Anything related to PowerPivot and DAX Formuale
kcourbet
Posts: 5
Joined: Fri Jan 05, 2018 1:32 am

Puzzling measure behaviour, yet simple (?) problem

Postby kcourbet » Fri Jan 05, 2018 1:54 am

Dear all,

I've been throwing myself at this for a while to no avail and I would appreciate if you could give me any pointer!

The data set is very simple:

table
data_id, spend, phase_id, discount_percent
0, 100, 0, 10%
0, 100, 1, 15%
1, 123, 0, 5%
1, 123, 1, 6%
Etc.

What I'm trying to get is the "gross_spend" (OK) and especially the "discount_value" (NOK)

gross_spend is spend divided by 1 - the phase 0 discount, which I define as follows:

Code: Select all

gross_spend:=CALCULATE(SUMX(table, DIVIDE([spend],1-[discount_percent])), table[phase_id]=0)


contract_value is "gross_spend" times "discount_percent", which I define as follows:

Code: Select all

contract_value:=SUMX(table, [gross_spend]*[discount_percent])


Now what happens is when I place "phase_id" in columns in a pivot table, I will get the correct results for phase_id = 0, but nothing for phase_id = 1 except when the phase_id = 0 discount equals the phase_id = 1 discount. This behaviour seems so gimmicky to me and I can't seem to wrap my head around it.

I have uploaded a sample output, by filtering on relevant rows (to show you one case where the discounts are equal. The majority is otherwise). As you can see the gross_spend value is correctly repeated across phase_id but contract_value is null as soon as the discount_percent is different.

Image
https://imgur.com/a/H24TU

Any idea what might be causing this? I suspect it has something to do w/ CALCULATE that I'm not seeing...

Many thanks!!

MattAllington
Posts: 968
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Puzzling measure behaviour, yet simple (?) problem

Postby MattAllington » Fri Jan 05, 2018 12:58 pm

There is a lot of complexity built into your formula here. You have 2 sumx formulas that are nested inside each other. This makes it very complex to calculate and also to debug. I suggest you try the following formula. I can’t guarantee it will work without testing it, but directionaly it should be better than you have. If it doesn’t work, you may be able to work it out from there.

Code: Select all

contract_value :=
SUMX (
    table,
    DIVIDE (
        table[spend],
        1
            - CALCULATE ( MAX ( table[discount_percent] ), table[phase_id] = 0 )
    )
        * table[discount_percent]
)
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

kcourbet
Posts: 5
Joined: Fri Jan 05, 2018 1:32 am

Re: Puzzling measure behaviour, yet simple (?) problem

Postby kcourbet » Sat Jan 06, 2018 8:53 am

Hello Matt

Thanks for the quick reply. This indeed seems to yield the desired result.

In this context, MAX could have been replaced with any other aggregation function, since it's inside the SUMX, right?

Again, thank you so much for your help. DAX is such a powerful tool.

Kevin

MattAllington
Posts: 968
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Puzzling measure behaviour, yet simple (?) problem

Postby MattAllington » Sat Jan 06, 2018 9:40 am

Yes. Max is just harvesting the single value in the column because you can’t refer to the column itself even though it has a single value. You could use any aggregation function or VALUES
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

kcourbet
Posts: 5
Joined: Fri Jan 05, 2018 1:32 am

Re: Puzzling measure behaviour, yet simple (?) problem

Postby kcourbet » Mon Jan 08, 2018 10:21 pm

After more thorough QC, unfortunately it seems the results are wrong. While the formula now returns values for every row (that's one thing), interestingly enough the actual results are only correct again for rows where phase_0 discount = phase_1 discount.

I tried to understand what the actual value returned for phase_1 was. To my surprise, it's equal to the following equivalent formula:

Code: Select all

contract_value_total_spend:=
SUMX (table, [total_spend] * [discount_percent])


You can see it on the example below:
sample2.png
sample2.png (22.36 KiB) Viewed 1774 times


It's as if the DIVIDE was being ignored (wth?).
Got any clue as to what's happening?

For reference, I'm using these formulas:

Code: Select all

contract_value :=
SUMX (
    table,
    DIVIDE (
        table[spend],
        1
            - CALCULATE ( MAX ( table[discount_percent] ), table[phase_id] = 0 )
    )
        * table[discount_percent]
)


Code: Select all

gross_spend :=
CALCULATE (
    SUMX (
        table,
        DIVIDE ( table[total_spend], 1 - table[discount_percent] )
    ),
    table[phase_id] = 0
)


In advance, many thanks

EDIT: I put together a small excel file that showcases the issue, to play around with.
power-pivot-problem-showcase.xlsx
(147.39 KiB) Downloaded 64 times

MattAllington
Posts: 968
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Puzzling measure behaviour, yet simple (?) problem

Postby MattAllington » Tue Jan 09, 2018 8:19 am

I'm happy to take a look, but please post a workbook that delivers the results you show in the image on your post. Remember you know you data - I don't. :-)
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

kcourbet
Posts: 5
Joined: Fri Jan 05, 2018 1:32 am

Re: Puzzling measure behaviour, yet simple (?) problem

Postby kcourbet » Tue Jan 09, 2018 7:56 pm

Sure, let me spell it out:

My problem: Finding out what "Gross Spend" and "Contract Value for Each Round" are.

This is our simplified data set (conceptually the same one as before). Only 12 rows of data.
data_set_2.png
data_set_2.png (55.1 KiB) Viewed 1752 times


I noticed the results varied whether the discount values were the same across rounds or not. So I added a dimension called "category" in order to slice by that.

Gross Spend
Conceptually gross spend is spend before round 0 discounts, so mathematically speaking gross_spend = spend / ( 1 - discount_percent[r0] ) .

For exploratory purposes, I created 5 "gross_spend" measures to see how they would behave (again, the problem is for round_id = 1). The only that really works at this point is the outer_calculate (no_filter obviously not, and the rest don't display values for round_id = 1).

    gross_spend_no_filter

    Code: Select all

    SUMX(the_table, DIVIDE([spend], 1-[discount_percent]))

    gross_spend_inner_calculate

    Code: Select all

    SUMX(the_table, DIVIDE(CALCULATE(SUM([spend]), the_table[round_id]=0) , 1 - CALCULATE(MAX([discount_percent]), the_table[round_id]=0)))

    gross_spend_outer_calculate

    Code: Select all

    CALCULATE(SUMX(the_table, DIVIDE([spend], 1-[discount_percent])), the_table[round_id]=0)

    gross_spend_filter

    Code: Select all

    CALCULATE(SUMX(the_table, DIVIDE([spend], 1-[discount_percent])), FILTER(the_table, [round_id]=0))

    gross_spend_filter_bis

    Code: Select all

    SUMX(FILTER(the_table, [round_id]=0), [spend]/(1-[discount_percent]))


At this point, we have correct results in the pivot table with the gross_spend_outer_calculate.
results_1.png
results_1.png (6.27 KiB) Viewed 1752 times


Contract Value
Now this is where things turn awry.

Conceptually, contract value[r] = gross_spend * discount_percent[r] (where r means round 0, round 1 ...).

If you write a simple contract_value:=SUMX(the_table, [gross_spend_outer_calculate] * [discount_percent]), you get the initial problem where you get no values for round 1, except when discount values are the same, as seen below:
contract_value_sumx.png
contract_value_sumx.png (17.11 KiB) Viewed 1752 times


If you write your solution, which is exploding the formula like so:

Code: Select all

contract_value_2 :=
SUMX (
    the_table,
    DIVIDE (
        [spend],
        1
            - CALCULATE ( MAX ( [discount_percent] ), the_table[round_id] = 0 )
    )
    * [discount_percent]
)


You get values for all cells but unfortunately, the resulting values are wrong for round_id = 1 (they are actually equal to [spend]*[discount_percent] for some mystery)
I can't attach another inline screen but you can see this in the file or here:
https://file.town/download/21nlfr2a57w3xqhpogpt6x6zi


Can you see the issue more clearly?

kcourbet
Posts: 5
Joined: Fri Jan 05, 2018 1:32 am

Re: Puzzling measure behaviour, yet simple (?) problem

Postby kcourbet » Tue Jan 09, 2018 7:59 pm

Here is the sample workbook (couldn't attach in previous post).
power-pivot-problem-showcase.xlsx
(152.82 KiB) Downloaded 58 times


PS: Mmm... It seems I've solved it somehow.

The following seems to return the correct result:

Code: Select all

contract_value:=
SUMX( VALUES( the_table[id] ), [gross_spend_outer_calculate] * LASTNONBLANK( [discount_percent], 1 ) )


Correct at the granular level, and also correctly additive at a higher level.

Huh. Now I have to study to fully understand that.

My thinking was to enforce values for each id by using VALUES. This would however return more than one rows, because id isn't unique. So I had to use aggregation for [discount_percent]. The only one that results in a correctly additive measure is LASTNONBLANK (MAX, AVERAGE etc would not yield the correct result at a higher level).

Would be happy to hear your thoughts about that !

PSS: Marco Russo's solution to this problem is the following:

Code: Select all

contract_value :=
SUMX (
    the_table,
    DIVIDE (
        spend,
        1
            - CALCULATE ( MAX ( disc ), round = 0, ALLEXCEPT ( the_table, the_table[id] ) )
    )
        * disc
)


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 4 guests