Calculate the difference between Numbers in the same row - different columns

Question

Sorry if it has been answered before, I just could not find it. I am trying to calculate the difference between columns in the same row, same calculation as Difference from a specific Value in excel pivot.

What the table says i.e in 1st row is that we had 39 different customers first registering on 2019/01 , then only 5 of them repurchased on 2019/02, only 2 of them in 2019/03 etc.

I want a new table where it shows the difference between 2019/01-2019/02, 2019/01-2019/03 etc. so under 2019/01 shows 0, under 2019/02 shows 34, under 2019/03 shows 37 etc for each row ( basically subtracting from the 1st value of the row)

Is it possible?

Raw Data

PBI Visual- I can create the first table as it is a simple count, but i cannot create the Loss and Loss Rate tables in PBI

is the image containing your input data? this is how data stored in table? Finally, if it is input data, show what output you wants. Output should based on sample input data with calculation details. — mkRabbani
I have uploaded both the raw data and how i want them visualized in PBI. is this what you asked for ? thank you for your effort so far . — Jojo
Hi @Jojo, I must appreciate your effort for creating sample data and output. But to much data and sample output always make thing difficult to understand. It would be great if your keep minimum sample data and output that covered all scenario. Sorry for to many change request. — mkRabbani

mkRabbani mkRabbani · Accepted Answer · 2020-08-28T10:38:19

Create a new custom column to your table as below-

column_january = "January"

Now for customer activity, add a Matrix in the report and configure the Matrix properties as below-

With some change in properties like - "Grand Total", "Sub Total" and "Per Row Level" you will get this below presentation-

Now for customer lost, Create a measure as below-

customer_lost = 

VAR current_year = MIN(your_table_name[activity date].[Year])

VAR customer_starts_with = 
CALCULATE(
    AVERAGE(your_table_name[customer]),
    FILTER(
        ALL(your_table_name),
        your_table_name[activity date].[Year] = current_year
            && your_table_name[activity date].[Month] = "January"
    )
)

RETURN IF(MIN(your_table_name[customer]) = BLANK(),BLANK(),customer_starts_with - MIN(your_table_name[customer]))

Now add a Matrix in the report and configure the Matrix properties as below-

With some change in properties like - "Grand Total", "Sub Total" and "Per Row Level" you will get this below presentation-

Your final measure for customer lost will be as below- (convert the measure type as %). Just copy your matrix for customer lost and replace replace measure "Customer Lost" with "Customer Lost Percentage" in the Values field for your presentation.

customer_lost_percentage = 

VAR current_year = MIN(difference[activity date].[Year])

VAR customer_starts_with = 
CALCULATE(
    AVERAGE(difference[customer]),
    FILTER(
        ALL(difference),
        difference[activity date].[Year] = current_year
            && difference[activity date].[Month] = "January"
    )
)

VAR customer_lost = IF(MIN(difference[customer]) = BLANK(),BLANK(),customer_starts_with - MIN(difference[customer]))

RETURN customer_lost/customer_starts_with

Calculate the difference between Numbers in the same row - different columns

2 Answers