Forum Discussion

RookiePBI2019's avatar
RookiePBI2019
Regular Visitor
6 years ago
Solved

Multiply measure by a factor

  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 o...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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's avatar
rsaprano
Most Valuable Professional
6 years ago

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