running total and cumulative count by descending order

[sreddy47652]

Hi Team, i have supplier id, Total sales and want show cumulative count and running total by desending order.  I have basic measures

Total sales= Sum(Sales[salesamount]) and running total , cumulative count measures.

I want to show total sales amount by desending order vice versa to show running total cumulative count in desending order so itried with different ways to achoeve visul level filter(but can't apply other measures) and ranking functionality but none of them is not working so can anyone help me to get Running Total and cumulative count measures with Desending order for Total sales with Running total and Cumulative count.

[Anonymous · Accepted solution]

Hi @sreddy47652 ,

Thanks to FreemanZ  and DataNinja777  for their quick replies. I have some other thoughts to add:

(1) We can create measures.

rank =
RANKX (
    SUMMARIZE ( ALL ( 'Sales' ), [supplier_id], "total", [Total sales] ),
    [total],
    [Total sales],
    ASC
)

Cumulative sales by rank =
VAR _table =
    SUMMARIZE (
        ALL ( 'Sales' ),
        [supplier_id],
        "total", [Total sales],
        "rank", [rank]
    )
VAR _rank = [rank]
RETURN
    CALCULATE ( [Total sales], FILTER ( _table, [rank] <= _rank ) )

(2) Then the result is as follows.

Best Regards,

Neeko Tang

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

[FreemanZ]

hi @sreddy47652 ,

try like:

running total =

VAR _sales = [Total sales]

VAR _result =

CALCULATE(

    [Total sales],

    FILTER(

        ALL(sales[supplier id]),

        [Total sales] <= _sales

    )

)

RETURN _result

cumulative count =

VAR _sales = [Total sales]

VAR _result =

CALCULATE(

    COUNT(sales[supplier id]),

    FILTER(

        ALL(sales[supplier id]),

        [Total sales] <= _sales

    )

)

RETURN _result

[sreddy47652]

Thanks for providing the above formulas but still it's giving  visual exceding memory issues even i have 15k records in my table. I need Totalsales values with cumulative and running total in descending order.

[DataNinja777]

Hi @sreddy47652 ,

I suppose your company's sales are made to customers and not suppliers 😉.  You can produce your required output by writing a measure like below:

Rank = 
CALCULATE (
    RANKX (
        ALL ( 'Sales'[salesamount] ),
        CALCULATE (
            SUM ( 'Sales'[salesamount] ),
            ALL ( 'Sales'[supplier_id] )
        )
    )
)

Using the Rank calculated column as shown above, cumulative sales measure is written as follows:

Cumulative sales by rank = 
CALCULATE (
    [Total sales],
    FILTER ( ALL ( 'Sales' ), 'Sales'[Rank] <= MAX ( 'Sales'[Rank] ) )
)

The resultant output will look like below:

I have attached an example pbix file for your reference:

Best regards,

[sreddy47652]

Thanks for providing the above formulas but still it's giving  visual exceding memory issues even i have 15k records in my table. I need Totalsales values with cumulative and running total in descending order.