Forum Discussion
Helper IContext transition on fact table
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])
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
10 Replies
- mangaus1111
Solution Sage
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
smkoOps sorry, thats the attribute from my calendar dimension table, I have change to the date column from sales table
- aj1973
Community Champion
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
- 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. - 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
