Forum Discussion

CTouu's avatar
CTouu
Frequent Visitor
3 years ago

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

  • CTouu , Try like

    CALCULATE

    ( sum(Table[Sales]) ,

    filter(ALL (Table), Table[D_Seller] = max(Table[D_Seller])

    )

    • CTouu's avatar
      CTouu
      Frequent 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.....

      • Kalorina's avatar
        Kalorina
        Frequent Visitor

        Hi,

        Try 

        Sales_Seller = CALCULATE( SUM('fact table'[Sales]), ALLEXCEPT('fact table', 'fact table'[Seller ID]) )

        And also for article and area separetly. 

  • CTouu's avatar
    CTouu
    Frequent Visitor

    I find a solution to my problem by using a SUMMARIZE function. 

    Thank you for your help