Forum Discussion
Subtotal on all rows - Performance issue with ALL functions
Hello
I'm stuck in a problem which seems to me really simple ..
MY NEED
I want a table with different level of aggregations but with the same KPI.
I don't want a matrix because I want to see my subtotal of each row --> I need to send this excel's extraction to my suppliers without any change.
I did a simple example :
This is my current table.
The finest level is Article so in my table my sales are splitted everywhere.
But I want this :
I want the subtotal for each Seller --> for example Jane sold 190€ and I want this subtotal on each row. Same for each area.
SO What I did :
Sales_seller =
CALCULATE
( sum(Sales) ,
ALL (D_Article)
)
AND Sales_Area =
CALCULATE
( sum(Sales) ,
ALL (D_Article),
ALL (D_Seller)
)
Theoretically, this is working but in reality, the measure is too loud and my table never generating.
I don't know how to do .. that seems so simple... In excel we can do it in like 2 seconds..
I tried to do it with matrix and applied subtotals etc but this doesn't work because I need to export my data in excel and to see the total of each dimensions on each row.
I hope this is quite clear..
Thank you by advance for your help
4 Replies
- amitchandakSuper User
CTouu , Try like
CALCULATE
( sum(Table[Sales]) ,
filter(ALL (Table), Table[D_Seller] = max(Table[D_Seller])
)
- CTouuFrequent Visitor
Hi,
Thks but it's not working, it's the same as what I did.
I got performance issues so that it's loading but never ending.....- KalorinaFrequent Visitor
Hi,
TrySales_Seller = CALCULATE( SUM('fact table'[Sales]), ALLEXCEPT('fact table', 'fact table'[Seller ID]) )
And also for article and area separetly.
- CTouuFrequent Visitor
I find a solution to my problem by using a SUMMARIZE function.
Thank you for your help