Optimize Measure with ranking and running totals

[George1973]

Hi All,

I have generated product ABC analysis based on three criteria.. Hits, Sales and Margin. The DAX Code works fine with relatively small set of records (when filtered) in the Product Table called "პროდუქცია" (Where the Product ID is "პროდუქციის კოდი").

However, when the records in the product table are moerthen, let's say 3K, then the measure takes a long period of time to be generated and in case of even more records, it give me an error about inssuficient memory in the visual (There is not enough memory to complete this operation..).

Please help to optimize the measure as much as possible 🙂

Thanks in advance,

Here is the code:

ABC Prod Analsis = 

VAR ParHigh=0.8
VAR ParMid=ParHigh+ParHigh*0.1875
VAR MrgHigh=0.4
VAR MrgMid=0.3
VAR MrgMidFin=if(MrgHigh<=MrgMid,MrgHigh,MrgMid)
VAR ProdCode=SELECTEDVALUE('პროდუქცია'[პროდუქციის კოდი])

//VAR Summary=SUMMARIZE(ALLSELECTED('პროდუქცია'),'პროდუქცია'[პროდუქციის კოდი],"ProdCode", [1st 12M Tot Hits],"cSales")


VAR summary = SUMMARIZE(
        ALLSELECTED('პროდუქცია'),
        'პროდუქცია'[პროდუქციის კოდი],
        "cSales", [1st 12M Tot Hits]
    )

//VAR summary = ADDCOLUMNS ( ALLSELECTED('პროდუქცია'), "cSales", [1st 12M Tot Hits])

 VAR HinRanks=RANKX(ALLSELECTED('პროდუქცია'),[1st 12M Tot Hits])

VAR RunTotHits=SUMX (
        TOPN ( HinRanks, summary, [cSales],DESC),
        [cSales]
    )
VAR GrToHits=SUMX ( summary, [cSales] )

//VAR GrToHits=CALCULATE([1st 12M Tot Hits],ALLSELECTED('პროდუქცია'))

VAR CummTotHits =
    DIVIDE ( RunTotHits,GrToHits, 0 )
VAR HitsRank=
    SWITCH (
        TRUE (),
        CummTotHits <= ParHigh, "A",
        CummTotHits > ParHigh
            && CummTotHits <= ParMid, "B",
        CummTotHits > ParMid,"C",
               
        "C"
    )

// -------------- Profit Ranking

VAR summary1 =
    ADDCOLUMNS ( ALLSELECTED( 'პროდუქცია'), "Prof", [1st 12M Tot Sales] )

 VAR ProfitRanks=RANKX(ALLSELECTED('პროდუქცია'),[1st 12M Tot Sales])

VAR RunTotProf=SUMX (
        TOPN ( ProfitRanks, summary1, [Prof], DESC ),
        [Prof]
    )
VAR GrTotProf=SUMX ( summary1, [Prof] )
VAR CummTotalProf =
    DIVIDE (RunTotProf,GrTotProf, 0 )
VAR ProfitRank=
    SWITCH (
        TRUE (),
        CummTotalProf <= ParHigh, "A",
       CummTotalProf > ParHigh
            && CummTotalProf <= ParMid, "B",
        CummTotalProf > ParMid,
             "C",
        "C"
    )

// ----------- Product Status Strategy Ranking

//VAR ProdStat=HitsRank&ProfitRank

VAR Marg=[1st 12M Margin]
VAR MargRank=
SWITCH(TRUE(),
Marg>MrgHigh,"A",
Marg>MrgMidFin && Marg<=MrgHigh,"B",
Marg<=MrgMidFin,"C",
"C")

VAR FinStat=ProfitRank&"-"&MargRank&"-"&HitsRank

Return
FinStat

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...