Multiply measure by a factor

[RookiePBI2019]

Dear all,

I am completely blocked, I would need to multiply a measure calculated by a conditional factor, I mean, I have created a formula to receive an average between 2 years depending the order type and now I have to multiply that measure by a factor conditioning the order type.

Order Type	Year	Qty
A	2019	3
A	2018	4
B	2019	5
B	2018	2
C	2019	6
C	2018	32

Order Type	Factor
A	0,33
B	1,05
C	1,15

could someone please help me?

Thanks in advance!

[Ashish_Mathur]

Hi,

Build a relationship from the Order Type column of Table1 to the Order Type column of Table2.  In Table1, write this calculated column formula

=RELATED(Table2[Factor])

To your visual, drag Order Type from Table2.  You may now create this measure

=SUMX(Table1,Table1[Factor]*Table1[Qty])

Hope this helps.

[v-frfei-msft · Accepted solution]

Hi @RookiePBI2019 ,

To create a measure as below.

Measure =
VAR averag =
    AVERAGE ( 'Table'[Qty] )
VAR ty =
    MAX ( 'Table'[Order Type] )
VAR fact =
    CALCULATE ( MAX ( Factor[Factor] ), FILTER ( Factor, Factor[Order Type] = ty ) )
RETURN
    averag * fact

For more details, please check the pbix as attached.

[rsaprano]

Hi @RookiePBI2019 

There are multiple ways to do this though I think the neatest is to use a separate order types (dimension) table that contains the factors and relate it to the measure with the order quantities as so:

You can then multiply your measure by the corresponding row in the factors table by using SUMX e.g.

Factored Quantity SUMX =

[Average Qty Per Year By Type] * SUMX(OrderTypeFactors,OrderTypeFactors[Factor])

Note that if you want to make sure that the measure is only shown against a specified order type (with no total), you could write a measure that picks out the relevant factor e.g:

Factored Quantity =

VAR OneOrderTypeSelected = HASONEVALUE(OrderTypeFactors[Order Type])

VAR SelectedOrderType = SELECTEDVALUE(OrderTypeFactors[Order Type])

VAR Factor = CALCULATE(VALUES(OrderTypeFactors[Factor]),OrderTypeFactors[Order Type]=SelectedOrderType)

RETURN

IF(OneOrderTypeSelected,[Average Qty Per Year By Type] * Factor,BLANK())

The resulting measures then look like:

A PBIX which shows this can be viewed here