Context transition on fact table

[smko]

I am aware context transition should not be performed on fact table due to possibility of duplicate rows. The row context of duplicate rows will become one unique filter context, this I do understand. I just want to understand how [Wrong] measure is being calculated for MP=Lazada, how the result become double of [Correct]. Appreciate any technical answers on this.

Dataset

Visual result

Sales = SUMX('Sales', 'Sales'[Quantity] * 'Sales'[UnitPrice])

Correct = SUMX('Sales', 'Sales'[Quantity] * 'Sales'[UnitPrice] * Sales[Com%])

Wrong = SUMX(Sales, Sales[Com%] * [Sales])

[mangaus1111 · Accepted solution]

In this example there is a multiplication between a measure [Sales] and a column [Com%], instead a multiplication between 2 columns. The scalar value of the measure [Sales] is always 16 beacause is independent from the row context.

Please hit the thumbs up & mark it as a solution if it helps you. Thanks.

MP                [Sales]           Com%

LAZADA          16        *       0.075    =     1.2  

LAZADA          16        *       0.075     =     1.2 

                                                              _________

                                                                2.4

[mangaus1111]

hi @smko ,

a measure has no row context, then if you use the [Wrong] measure you get the following calculation:

MP                [Sales]           Com%

LAZADA          16        *       0.075    =     1.2  

LAZADA          16        *       0.075     =     1.2 

                                                              _________

                                                                2.4

[mangaus1111]

Hi @smko ,

the measure Wrong = SUMX(Sales, Sales[Com%] * [Sales]) is wrong and give you the double of the correct one, because "a measure is an expression where there is no row context".

The scalar value computed by the measure [Sales] is always 16 by Lazada and the scalar value computed by the [Wrong] Measure is:

MP               [Sales]       Com%

LAZADA       16         *     0.075    =   1.2

LAZADA       16        *      0.075    =   1.2

                                                    --------------

                                                          2.4

Please hit the thumbs up & mark it as a solution if it helps you. Thanks.

[aj1973]

Hi @smko 

Where is this column coming from?

try to use the column Date from the Table of the dataset

[smko]

Ops sorry, thats the attribute from my calendar dimension table, I have change to the date column from sales table

[aj1973]

Therefore you either add MonthYearNum to the fact table and use it in your visual or change the relationship to bidirectional between the Fact table and Calendar Table

[smko]

The column [Date] and [MP] in the visual are from fact table

[aj1973]

Here you go

[smko]

I just want to understand how the [Wrong] measure come up with wrong result. I already knew the correct DAX to get the right result as shown in [Correct] measure

[aj1973]

Very good question, I invite to watch theses videos to explain it in details

https://www.youtube.com/watch?v=lMuDz6ViU1w

https://www.youtube.com/watch?v=lX2ztEh3tuA

https://www.youtube.com/watch?v=Nm9x83J-SBk