Cumulative sales per day and other fields

[arturocastillo]

Hello,

I'm designing a report for a sales analysis. However, I have the next problem. I want to present a cumulative sales per day and per service and region of my company but when I show this, the cumulative does not show the correct amount but repeats the total sum.

But when I don't use the field region or service the cumulative per day is correct

Actually I am using the next formula:

cumulative = SUMX(FILTER(ALLSELECTED('table_sales'),'table_sales'[date]<=MAX('table_sales'[date])),'table_sales'[quantity])

When I use the tipical formula I don´t have any difference:

cumulative = CALCULATE (
        SUM ( 'table_sales'[quantity] ),
        FILTER (
            ALLSELECTED ( 'table_sales'),
            'table_sales'[date] <= MAX ( 'table_sales'[date] )
        )
    )

I expect have the next result:

Is it necessary add any other function to my formula?

[Vvelarde · Accepted solution]

@arturocastillo

Hi, one way to solve this:

Create a calculated Column

Index =
VAR RegionRank =
    RANKX ( Table1, Table1[Region],, DESC, DENSE )
VAR ServiceRank =
    RANKX ( Table1, Table1[Service],, DESC, DENSE )
RETURN
    ( [Day] * 100 )
        + ( RegionRank * 10 )
        + ServiceRank

After that Create a New Measure

RunningTotal =
CALCULATE (
    SUM ( 'Table1'[Sales] ),
    FILTER ( ALLSELECTED ( Table1[Index] ), Table1[Index] <= MAX ( Table1[Index] ) ),
    ALLEXCEPT ( Table1, Table1[Index] )
)

Maybe You need some adjust to sort the table visual.

Regards

Victor

Lima - Peru

[arturocastillo]

Thanks @Vvelarde! The help you gave me by private messagges was very good. I have some fields which help me to classify my data and them have to add in an exception.

The final formula was:

RunningTotal =
CALCULATE(
SUM('Table1'[Sales]);FILTER(ALLSELECTED(Table1[Index]);Table1[Index]<=MAX(Table1[Index]));ALLEXCEPT(Table1;Table1[Index];Table1[Region];Table1[Service]))

[arturocastillo]

@Vvelarde

Hi, I still have the same problem. The cumulative doesn't show like I expect. We can continue the conversation in spanish by private messages?

[v-yulgu-msft]

Hi @arturocastillo,

You can firstlt add an index column in Query Editor mode.

Then, create a measure like below:

Running value =
CALCULATE (
    SUM ( table_sales[sales] ),
    FILTER ( ALL ( table_sales ), table_sales[Index] <= MAX ( table_sales[Index] ) )
)

Best regards,

Yuliana Gu