Sum Totals for the Products of multiple Measures based on Single Column, with multiple Filters

[Anonymous]

Hi,

I have two measures (Price & Volume) that both do a sum on the same table and same columns but based on different filters on that specific table. I also have a measure (EURTotal) that multiplies these two measures. Currently it also multiplies the totals, however, I want it to SUM per period (YearMonth). These periods come from a separate Calendar table, linked to the table 'FACT'.

The current (simplified) dataset is used:

Sample data, Table 'FACT'

Date	Subject	Quantity_Type	Quantity
2020-01-01	TB	Volume	10
2020-02-01	TB	Volume	200
2020-03-01	TB	Volume	30
2020-04-01	TB	Volume	70
2020-01-01	TB	Price	2
2020-02-01	TB	Price	5
2020-03-01	TB	Price	4
2020-04-01	TB	Price	3

Measure 'Price':

Price =
CALCULATE(
    SUM('FACT'[Quantity]),
    FILTER('FACT', 'FACT'[Subject] = "TB"),
    FILTER('FACT', 'FACT'[Quantity_Type] = "Price")
    )

And 'Volume':

Volume =
CALCULATE(
    SUM('FACT'[Quantity]),
    FILTER('FACT', 'FACT'[Subject] = "TB"),
    FILTER('FACT', 'FACT'[Quantity_Type] = "Volume")
    )

By simply multiplying I get, obviously, not a SUM in the total column, but a PRODUCT of the two SUMS. As you can see here:

Current result:

Date	Volume	Price	Vol*Price
2020-01-01	10	2	20
2020-02-01	200	5	1000
2020-03-01	30	4	120
2020-04-01	70	3	210
Total	310	14	4340.00

What I tried is to use SUMX, but somehow multiplying SUMX * SUMX it gives the exact same result as SUM * SUM.

Expected result:

Date	Volume	Price	EURTotal
2020-01-01	10	2	20
2020-02-01	200	5	1000
2020-03-01	30	4	120
2020-04-01	70	3	210
Total	310	14	1350.00

What I specifically tried is the following:

EURTotal =
CALCULATE(
    SUMX('FACT'[Quantity]),
    FILTER('FACT', 'FACT'[Subject] = "TB"),
    FILTER('FACT', 'FACT'[Quantity_Type] = "Price")
    )
*
CALCULATE(
    SUMX('FACT'[Quantity]),
    FILTER('FACT', 'FACT'[Subject] = "TB"),
    FILTER('FACT', 'FACT'[Quantity_Type] = "Volume")
    )

This doesn't work, but I don't see how I could get it in a working SUMX format like the following, for instance:

EURTotal = SUMX (FACT,

FACT[Quantity] --but then a filter here on Price

* FACT[Quantity] --but then a filter here on Volume

)

which I found here: https://www.burningsuit.co.uk/blog/2018/07/dax-is-the-total-row-in-a-table-giving-you-grief/

Any suggestions?

Kind regards,

Igor

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Have you tried using "pivot column" in the query editor?

Then try this measure:

Measure = CALCULATE(SUMX(VALUES('Table'[Date]),CALCULATE(SUM('Table'[Price])*SUM('Table'[Volume]))))

Sample .pbix

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

[amitchandak]

@Anonymous , Try measure like

sumx(summarize(Table, Table[date], table[Subject], "_1" ,[price] , "_2",[volume]),[_1]*[_2])

[Anonymous]

Thanks for the quick response!

Unfortunately, I get the exact same result. I used this measure to try it out:

EURTotal =
    SUMX(
    SUMMARIZE('FACT', 
        'DIM Kalender'[Datum], --same result as 'FACT'[Date]
        'FACT'[Subject], 
        "_1", [Volume],
        "_2", [Price]),
    [_1]*[_2])

So the Total still has the value of the multiplication of the Total Volume * Total Price.

Kind regards,

Igor