Custom Total having Sum and Sumproduct in Matix

[ashrat001]

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

[Nathaniel_C]

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

[ashrat001]

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

[Nathaniel_C]

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

[ashrat001]

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