Problem with Cumulative Total% - Category ABC

[pbl19]

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.

[Anonymous]

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

[Anonymous]

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]

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

[Ashish_Mathur]

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]

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]

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.