Multiplying measures together using sumx and group by columns

[jonasgudm]

Hello I'm trying to build a measure which is a sumproduct of two measures using this data:

COMPYEARHIST	YEARMONTHSALE	PRESDEPART	PRESASK	HISTASK	MONTHSALE
2016	202206	202206	188	198	72392
2016	202206	202208	211	208	98921
2016	202206	202207	213	209	113555
2017	202206	202206	188	220	72392
2017	202206	202208	211	233	98921
2017	202206	202207	213	234	113555
2016	202207	202208	211	208	104539
2016	202207	202207	213	209	74069
2017	202207	202208	211	233	104539
2017	202207	202207	213	234	74069

I have then added 3 measures.

-ASKRatio which is a ratio between PRESASK AND HISTASK:

ASKRatio = 
CALCULATE(
    DIVIDE(
        SUM(AdjustRatioNew[PRESASK]),
        SUM(AdjustRatioNew[HISTASK])
    ))

-MonthSaleTotal which is the sum of MONTHSALE for each COMPYEARHIST and YEARMONTHSALE

MonthSaleTotal = 
CALCULATE(
    sum(AdjustRatioNew[MONTHSALE]),
    ALLSELECTED(AdjustRatioNew[PRESDEPART])
    )

-SaleRatio which is a ratio between each MONTHASLE and TotalSalesMonth

SaleRatio = sum(AdjustRatioNew[MONTHSALE])/[MonthSaleTotal]

Then what I'm missing is a sumproduct called ASKSaleAdj between ASKRATIO and SaleRatio for each YearMonthSale and COMPYEARHIST, basically ending in this:

COMPYEARHIST	YEARMONTHSALE	PRESDEPART	PRESASK	HISTASK	ASKRatio	MONTHSALE	TotalSaleMonth	SaleRatio	ASKSaleAdjust
2016	202206	202206	188	198	0,95	72392	284868	0,25
2016	202206	202208	211	208	1,01	98921	284868	0,35
2016	202206	202207	213	209	1,02	113555	284868	0,40	1,00
2017	202206	202206	188	220	0,84	72392	284868	0,25
2017	202206	202208	211	233	0,91	98921	284868	0,35
2017	202206	202207	213	234	0,91	113555	284868	0,40	0,89
2016	202207	202208	211	208	1,01	104539	178608	0,59
2016	202207	202207	213	209	1,02	74069	178608	0,41	1,02
2017	202207	202208	211	233	0,91	104539	178608	0,59
2017	202207	202207	213	234	0,91	74069	178608	0,41	0,91

So basically to sumproduct for the ASKSaleAdjust = 0,89 for compyearhist = 2017 and yearmonthsale = 202206 we do:
0,84*0,25+ 0,91*0,35+0,91*0,4 = 0,89

And could then be shrinked to this:

COMPYEARHIST	YEARMONTHSALE	ASKSaleAdj
2016	202206	1,00
2017	202206	0,89
2016	202207	1,02
2017	202207	0,91

If you could show me how to do the formula for the measure ASKSaleAdj or have another method, that would mean the world to me 🙂 

[jonasgudm]

@v-mengzhu-msft , I have edited the post.
Hopefully it is more understandable now

[v-mengzhu-msft]

Hi @jonasgudm ,

I have something I don't understand, for example, the column "ASKSaleAdj" you need

what kind of operation is used to find out his data?

Or do you already have the data for this column and just don't understand how to  shrinked to be what you want?

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly