Forum Discussion

pbl19's avatar
pbl19
Frequent Visitor
5 years ago

Problem with Cumulative Total% - Category ABC

Hi,

I'm at an absolute loss as to how to calculate a cumulative total. I've tried googling, reading the forums, following the documentation, decomposing the calculation, but my measure always fails and the error is "Not enough memory".
It's very strange, my PC have 8gb ram, ssd, ryzen 5, 64bits (OS and Power BI).

The problem i think comes for the measure. The size of the dataset is not very large.

 

I need to achieve a table like the next image, but that would be recalculated, in other words, it would be dynamic according to the selected filter. (e.g.: SUC_ID, CANAL_ID)

https://www.dropbox.com/s/c1an7o1wazug4x9/dataset.pbix?dl=0     ---> File

 

Sorry for my English if there is something that is not clear.
Any help would be greatly appreciated.

Need Help

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    The column names are not in English so i do not understand what you want.  Which is the Category column?  Which is the Sales column?

    • pbl19's avatar
      pbl19
      Frequent Visitor
      5 years ago

      hi Ashish_Mathur ,
      Sales Column is TOTALITEM_CI.
      The category measure will be created based on the cumulative percentage measure. (80/20 pareto).

       

      DIM_ARTICULO --> Product Table 
      DIM_SUCURSAL -- > branch table

      Thank you.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        Because of the sheer size of the Data, time taken to process is very high.  I wrote these 2 measures

        Rank = RANKX(ALL(DIM_ARTICULO[ART_ID]),[SALES],,0,SKIP)
        Cumulative revenue = SUMX(TOPN([Rank],CALCULATETABLE(VALUES(DIM_ARTICULO[ART_ID]),ALL(DIM_ARTICULO[ART_ID])),[SALES]),[SALES])

        When i dragged the second measure to the visual, it did not appear in the visual.

        Sorry but cannot help here. 

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  pbl19 ,

    You can create these measures.

    Sale =CALCULATE(SUM('FT_STOCKVENTA'[TOTALITEM_CI]),FILTER(ALL('DIM_ARTICULO'),'DIM_ARTICULO'[ART_ID]=MAX('DIM_ARTICULO'[ART_ID])))
    Cumulativesales =CALCULATE(SUM('FT_STOCKVENTA'[TOTALITEM_CI]),FILTER(ALL('DIM_ARTICULO'),'DIM_ARTICULO'[ART_ID]<=MAX('DIM_ARTICULO'[ART_ID])))
    Cumulativesale% =
DIVIDE([Sale],[Cumulativesales])
    Category=
IF(
    [Cumulativesale%]>+0&&[Cumulativesale%]<=0.8,"A","B")

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • pbl19's avatar
      pbl19
      Frequent Visitor
      5 years ago

      Thanks u Liu for your comment,  but when i dragged the measurements to the visual, it crashed.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  pbl19 ,

     

    Your data volume is too large, you can try to put the measure into the visual object one by one, and after the result of one measure comes out, put it into another measure.

    Or, you can create a sample with less data based on your data to try whether these measures meet your expected results.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly