Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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
Proud to be a Super User!
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
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
Proud to be a Super User!
Hi Nathaniel,
See below your suggested Dax it is not working properly. I've faced an error.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
19 | |
13 |
User | Count |
---|---|
125 | |
40 | |
32 | |
27 | |
24 |