Accumulated data without date

[kusanagi]

Dear all experts,

I am currently transforming an excel file to PBI, but I cannot find a correct formula for column D.

Column B is a measure: qty = count('table'[ID])

Column C is also using [qty], but show value as percent

The order of the row is decending [qty]

Coulmn D should be the cumulated data of all the rows in the front, and it will add up to 100% in the last row.

In Excel: D4=D3+C4; D5=D4+C5; D6=D5+C6

I try to look up a solution, but all the solutions I found are based on the date, so those are not for me.

Thanks for your support

[mdaatifraza5556 · Accepted solution]

Hi @kusanagi 

Please check your rate measure

3A01----Rate should be 54.69

Create a measure for running total 

running total =

    VAR _rnk = RANKX(

            ALLSELECTED('Table'[Item]),

            [qty_m],,

            DESC,

            Dense

           )

     VAR r_t =

    CALCULATE(

        [qty_m],

        FILTER(

            ALLSELECTED('Table'[Item]),

            _rnk >= RANKX(

                    ALLSELECTED('Table'[Item]),  

                    [qty_m],              

                    ,                

                    DESC,        

                    DENSE    

        )

        )

    )

    RETURN

    r_t

Create measure for total (overall)

total_m =

             CALCULATE(

                SUM('Table'[Qty]),

                ALLSELECTED('Table')

             )

last measure to get your result

cum_% =

    DIVIDE( [running total], [total_m], 0)

If this answers your questions, kindly accept it as a solution and give kudos.

[V-yubandi-msft]

Hi @kusanagi ,

I wanted to follow up regarding the information @MFelix  provided. It appears to align with your initial requirements and may support our progress.

If you are still reviewing or require further information, please let us know. We are available to assist as needed.

Thanks 

[V-yubandi-msft]

Hi @mdaatifraza5556 ,

Thank you for being part of the Microsoft Fabric Community. Special thanks to @MFelix  for suggesting the use of visual calculations with RUNNINGSUM(), which is a good  fit for your scenario with percentage values sorted in descending order and no date dependency.

I appreciate your quick participation in the discussion @kusanagi , @MFelix .

Best regards,
Yugandhar _CST Team.

[Shahid12523]

use this

cum_pct =
VAR Rank = RANKX(ALL('table'[Item]), [qty], , DESC, DENSE)
RETURN
DIVIDE(
SUMX(
FILTER(ALL('table'), RANKX(ALL('table'[Item]), [qty], , DESC, DENSE) <= Rank),
[qty]
),
CALCULATE([qty], ALL('table'))
)

[MFelix · Accepted solution]

Hi @kusanagi ,

This is a good use case for the visual calculations that will allow you to have a cumulative calculation over the rate values.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview

In this case you need to use a runningsum over the percentage the syntax will be similar to this_:

Running sum = RUNNINGSUM([%GT qty], ORDERBY([qty], DESC))

[mdaatifraza5556 · Accepted solution]

Hi @kusanagi 

Please check your rate measure

3A01----Rate should be 54.69

Create a measure for running total 

running total =

    VAR _rnk = RANKX(

            ALLSELECTED('Table'[Item]),

            [qty_m],,

            DESC,

            Dense

           )

     VAR r_t =

    CALCULATE(

        [qty_m],

        FILTER(

            ALLSELECTED('Table'[Item]),

            _rnk >= RANKX(

                    ALLSELECTED('Table'[Item]),  

                    [qty_m],              

                    ,                

                    DESC,        

                    DENSE    

        )

        )

    )

    RETURN

    r_t

Create measure for total (overall)

total_m =

             CALCULATE(

                SUM('Table'[Qty]),

                ALLSELECTED('Table')

             )

last measure to get your result

cum_% =

    DIVIDE( [running total], [total_m], 0)

If this answers your questions, kindly accept it as a solution and give kudos.