Forum Discussion
Solved
Calculate sumproduct with variable values
I need to create a certain measure that calculates:
| Date | Quantity |
| 2025/01/01 | 69 |
| 2025/01/02 | 69 |
| 2025/01/03 | 69 |
| 2025/01/01 | 120 |
| 2025/01/02 | 120 |
| 2025/01/01 | 200 |
| 2025/01/01 | 200 |
| 2025/01/02 | 200 |
I need a measure that calculates a kind of sumproduct, in which you need to multiply the quantity by the number of days it was implemented, then add these results together and finally, divide everything by the total number of days. Basically, make the following numerical expression:
((69 * 3) + (120 * 2) + (200 * 2))/3
In this previous example, the result on a card should be approximately 282.3
Hi, would this be the result you were looking?
Measure = VAR _SummaryTable = SUMMARIZECOLUMNS( 'Sample'[ Quantity], "DaysCount", DISTINCTCOUNT('Sample'[Date]) ) VAR _Numerator = SUMX( _SummaryTable, [ Quantity] * [DaysCount] ) VAR _Denominator = DISTINCTCOUNT('Sample'[Date]) VAR _Result= DIVIDE(_Numerator, _Denominator) RETURN _Result
3 Replies
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Hi, would this be the result you were looking?
Measure = VAR _SummaryTable = SUMMARIZECOLUMNS( 'Sample'[ Quantity], "DaysCount", DISTINCTCOUNT('Sample'[Date]) ) VAR _Numerator = SUMX( _SummaryTable, [ Quantity] * [DaysCount] ) VAR _Denominator = DISTINCTCOUNT('Sample'[Date]) VAR _Result= DIVIDE(_Numerator, _Denominator) RETURN _ResultHi,
PBI file attached.
Hope this helps.
