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 order type and now I have to multiply that measure by a factor conditioning the order type.

 

Order TypeYearQty
A20193
A20184
B20195
B20182
C20196
C201832

 

Order TypeFactor
A0,33
B1,05
C1,15

 

could someone please help me?

 

Thanks in advance!

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.

     

3 Replies

  • 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

     

     

     

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    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.

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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.