topic Custom Total having Sum and Sumproduct in Matix in DAX Commands and Tips

Custom Total having Sum and Sumproduct in Matix

ashrat001 — Fri, 04 Jun 2021 18:24:02 GMT

Hi Experts,

Hope you are well. I have a requirment in which I have a few KPIs/measures in a Matrix. I want to apply a custom total in the end of my Matrix but this custom total should apply Sum function for a few KPIs/measures and Sumproduct on other KPIs/measure. Below is the sample:

	Product 1	Product 2	Product 3	Custom Total
Sale	10	15	30	Sum
Production	20	22	40	Sum
Discount	2	4	3	Sumproduct
Cost	500	600	700	Sumproduct
Profit	1200	1500	1600	Sumproduct

How to achieve this? Thanks!

TA

Re: Custom Total having Sum and Sumproduct in Matix

Nathaniel_C — Sat, 05 Jun 2021 05:15:24 GMT

Hi ashrat001 please try this:

Special = VAR _totsum = SUMX ( myTable, myTable[Product 1] + myTable[Product 2] + myTable[Product 3] ) VAR _totSumProduct = PRODUCTX ( myTable, [Product 1] * myTable[Product 2] * myTable[Product 3] ) RETURN IF ( MAX ( myTable[TYPE] ) = "Production" || MAX ( myTable[TYPE] ) = "Sale", _totsum, _totSumProduct )

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Re: Custom Total having Sum and Sumproduct in Matix

ashrat001 — Sat, 05 Jun 2021 08:31:59 GMT

Hi Nathaniel,

Thanks for your reply. Maybe you did not get the requiremt. Actually, in the column I have a dimension namely variant having different products name and in the value section I have different measures which I need to sum and do the productsum. Please is the screen shot of my matrix having actuall data. For measure Production and Sales I need to sum these and other measures like NSI, Cost of Blend, Efficiency Loss and Other Chemicals I need to apply Sumproduction on these using measures Production and Sales.

I did not get your Dax.

I want this Special Total at the end of the Matrix. Thanks!

TA

Re: Custom Total having Sum and Sumproduct in Matix

Nathaniel_C — Sat, 05 Jun 2021 17:15:28 GMT

Hi ashrat001 , edited measure for you:

SumProduct vs Sum = var _totalSum = SUMX(myTable,myTable[Product 1]+myTable[Product 2]+myTable[Product 3]) var _totSumProduct = Sumx( myTable,[Product 1]*myTable[Product 2]*myTable[Product 3]) var _iterate = IF(MAX(myTable[TYPE])="Production" || MAX(myTable[TYPE])="Sale",_totalSum,_totSumProduct) return sumx( myTable,_iterate) ================================================ Final measure = var _mycolumn = ADDCOLUMNS(myTable,"ColSum",[SumProduct vs Sum]) var _sumcolumn = SUMX(_mycolumn,[SumProduct vs Sum]) return _sumcolumn

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Re: Custom Total having Sum and Sumproduct in Matix

ashrat001 — Sun, 06 Jun 2021 08:44:38 GMT

Hi Nathaniel,

See below your suggested Dax it is not working properly. I've faced an error.

SumProduct vs Sum =

var totalSum = sumx('Merged Queries','Merged Queries'[Variant.Variant Level 01])

var totSumProduct = Sumx( 'Merged Queries','Merged Queries'[Variant.Variant Level 01] * 'Merged Queries'[Variant.Variant Level 01])

var iterate = IF(MAX('Merged Queries'[Fibre Production Tons])="Production" || MAX('Merged Queries'[Sales Volume Tons])="Sale",totalSum,totSumProduct)

return sumx('Merged Queries',iterate)

I do not understand the [Product 1] + [Product 2]
What are you summing in the above epressison. Product 1 and Product 2 are the values of the dimension variant not a measure.
Secondly, [Product 1] * [Product 2]
Following is the formula for Sumproduct
SUMPRODUCT(D20:X20,D10:X10)/AB10)
Multiplying the each value of the Cost with Production for the specific Variant value (Product) and then dividing by total of the Production.

I would appreciate your assistance. Thanks