How to do a cumulative with a duplicate value

Question

[http://uupload.ir/view/v2t_capture.png] We have 2 tables with 2 columns:

table location ->column ->city ,province
table sales column ->actual sales

table location : table sales 
province  city       id     sales
tehran    eslamshar  1      100
tehran    rey        2      500
hamedan   tefresh    3      500
esfahan   esahan     4      400
gilan     rasht      5      400
gilan     rar        6      900

I want to calculate 80% of total sales in each city and each province it means if we have 2800 total sale in whole province, we want just show province that cut off 80 % of sales:

I use this measure but it has one problem that duplicate value does not count in a cumulative sum.

If I have two same sale like 900 it does not count in cumulative and it ignores it.

Cumulative Total =
IF (
    NOT ( ISBLANK ( [sales] ) ),
    CALCULATE (
        [sales],
        FILTER (
            ALL ( DimLocation[Province] ),
            CALCULATE ( [sales], VALUES ( DimLocation[Province] ) ) <= [sales]
        )
    )
)

Expected Result:

        sales   cumulative
tehran  800 800
shiraz  200 1000
ghom    200 400
markazi 300 500

Output:

tehran  800 800
shiraz  200 1000
ghom    200 1000
markazi 300 500

Could you provide sample data that matches the expected result? — mxix
i put link for more explain uupload.ir/view/v2t_capture.png i want yellow result for Cumulative Sum but i got red result for Cumulative Sum As I mentioned above this measure ignore duplicate value (In this example, only one of the 200 is calculated) — LEILA
In summary, how cumulative aggregation based on the province's column is obtained by the highest amount to the lowest value, so that all values, even duplicates, are counted in cumulative aggregates — LEILA
You still have not provided sample data and output that actually match! for example "markazi" is not in the sample data! — mxix
could you please ignore all of above detail .i have a table in this link : with two column city and sales pasteboard.co/IoRmArs.png how to write a measure to cumulative sales? i want a result exactly commutative column in the link pasteboard.co/IoRmArs.png Two points to consider :1 - Based on the amount of sort from the highest to the lowest 2-calculate duplicate values like 800 in cumulative — LEILA

mxix mxix · Accepted Answer · 2019-07-22T10:37:37

For a running total you could create this measure:

RT Province Sales := 
VAR _current = SELECTEDVALUE ( DimLocation[Province] )
RETURN
    CALCULATE (
        [Sales], 
        DimLocation[Province] <= _current , 
        ALL ( DimLocation[Province] )
    )

How to do a cumulative with a duplicate value

1 Answers