Forum Discussion
jobf
Helper II
Helper IICalculate 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
- Jihwan_Kim
Super User
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
- MasonMA
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 - Ashish_Mathur
Hi,
PBI file attached.
Hope this helps.
