Forum Discussion

HarryS's avatar
HarryS
Icon for Helper I rankHelper I
6 years ago

Cumulative sum sorted by another column in another table

Hello, Basically, I have one table with data zones and beneath them postcodes (multiple postcodes make up data zones). In another table, I have rows of sales details, which include postcodes. So, ...
Need Help
v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi  HarryS ,

 

We can create a measure and sort by it to meet your requirement:

 

Cumulative total = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Sales] < MIN ( 'Table'[Sales] )
            || ( 'Table'[Data zone] <= MIN ( 'Table'[Data zone] ) && 'Table'[Sales] = MIN ( 'Table'[Sales] ))
    )
)

 

 


Best regards,

 

HarryS's avatar
HarryS
Icon for Helper I rankHelper I
6 years ago

Hello,

Thanks for your replies. Unfortunately I still can't seem to get it to work. I have solved the two tables problem by going back into Excel and creating a lookup column. I think part of the issue is probably that I presented an overly simplified account of my data. The relevant columns are as follows

Datazone                 Value
1                                1
2                                0
2                                0
3                                1
4                                1
5                                0
6                                0
3                                1
1                                1
3                                0

And so on. I would like to arrange the above table (on a much larger scale), in a cumulative total, ordered by total value, rather than Datazone number. Everything I have tried so far either keeps the Datazone number as the order and does a cumulative total based on that, or gives me wildly inflated figures.

Again, thanks for taking the time to reply and help me with this, I do appreciate it!