How to caculate a special cumulative rate ?

[Anonymous]

Hi,

I have sales data (from 2016 to today). I want to vizualize the rate of refund basing on the amount (in euros). 

rate :

- numerator = amount of refund sales (in euros but nevermind) refunded at this point or before

- denominator = total amount of sales (refund or not), for the total period

example : for 2017

- total 2017 : 10 000€

- 1 refund of 200€ the 15th january

- 1 refund of 300€ the 15th july

I will have 0/10000 until 15th junuary, then 200/10000 until 15th july and after that i will have 500/10000 (5%)

I wrote that for the numerator

rate = 
CALCULATE (
    SUM ( tableSales[amount] ), tableSales[refund]=="YES",
    FILTER (
        ALL ( tableSales ),
        tableSales[date] <= MAX ( tableSales[date] )
    )

I have two problems with that :

- it takes everything before the actual date of x axis, even if it's before the observed period (example : for 2017, it will also takes refunded sales in 2016...)

- it's only the numerator, I dont know how to take into account the total amount on the whole given period (period = years for some plots, and trimester for others).

Any idea to how update that formula ?

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

TableSales:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is no relationship between two tables. You may create measures as below.

numerator = 
CALCULATE(
    SUM(TableSales[Amount]),
    FILTER(
        ALLSELECTED(TableSales),
        TableSales[Date]<=MAX('Calendar'[Date])&&
        YEAR(TableSales[Date])=YEAR(MAX('Calendar'[Date]))&&
        TableSales[Refound]="Yes"
    )
)

Percentage = 
DIVIDE(
    [numerator],
    CALCULATE(
        SUM(TableSales[Amount]),
        FILTER(
            ALLSELECTED(TableSales),
            YEAR(TableSales[Date])=YEAR(MAX('Calendar'[Date]))&&
            TableSales[Refound]="Yes"
        )
    )
)

Result:

Best Regards

Allan

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

[parry2k]

@Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

and add a measure using date dimension, instead of ALL use ALLSELECTED

rate = 
CALCULATE (
    SUM ( tableSales[amount] ), tableSales[refund]=="YES",
    FILTER (
        ALLSELECTED ( tableDate[Date] ),
        tableDate[date] <= MAX ( tableDate[date] )
    )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi,

I dont get it at all... Are you sure it's 3 times "tableDate[Date]" ? It's never "tableSales[Date]" ?

And it's about a best practice but what about my 2 initials problems?  ^^ You agree it doesnt solve it ?

[parry2k]

@Anonymous yes anywhere you are referring to the date in your measure, you will use new added Date dimension (assuming it is called tabelDate

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

But how about my 2 initials problems ?

[parry2k]

@Anonymous not sure what you are referring to initial two problems?

[Anonymous]

---

I have two problems with that :

- it takes everything before the actual date of x axis, even if it's before the observed period (example : for 2017, it will also takes refunded sales in 2016...)

- it's only the numerator, I dont know how to take into account the total amount on the whole given period (period = years for some plots, and trimester for others).

[Anonymous]

@parry2k 

I tried to create 2 date tables :

- 1 date table (date) to x-axis

- 1 date table (date2) to the legend

and do that :

rate = 
CALCULATE (
    SUM ( tableSales[amount] ), tableSales[refund] =="YES",
    FILTER (
        ALLSELECTED( tableSales[dateRefund]) ,
         tableSales[dateRefund] <= MAX (  date[Date] )
    ),    FILTER (
        ALLSELECTED( tableSales[dateCommand]) ,
         YEAR(tableSales[dateCommand]) == YEAR(date2[Date]
    )
)

but it doenst work

[Anonymous]

Concerning your tips, it didnt works. It works if I change as follow :

CALCULATE (
    SUM ( tableSales[amount] ), tableSales[refund]=="YES",
    FILTER (
        ALLSELECTED ( tableSales[Date] ),
        tableSales[date] <= MAX ( tableDate[date] )
    )

And I put tableDate[date] as x-axis on the plot