Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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 🙂
@Anonymous , I have edited the post.
Hopefully it is more understandable now
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
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.
User | Count |
---|---|
13 | |
11 | |
9 | |
8 | |
8 |