cancel
Showing results for
Search instead for
Did you mean:  Helper II

## How to calculate Delta in a matrix with different measures

Hello,

i created a matrix with different measures for each country. The month is in the raw (see screenshot). I want to show different calculations in the column after September. For example the delta betbween the number of orders in france between August and September. In this example the delta is -50%. In the next column i want to calculate the delta between the average number of orders per month and the last month. In this case (1+4+2) = 7 and the average in 3 months is 2,3333. So the number of oders in September towards the average of the last 3 Months is -14,28% (2/2,3-1)

In the next raw i want the same calculation but for the turnover and in the other raw for the number of orders.

If you need it i can upload an test file.

Is this possible?

Thanks in advance.

Best Regards

7 REPLIES 7  Community Support

Hi, @Wedding55

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

Table: You may create two measures as below.

``````Delta Last Month =
var m = MAX('Table'[YearMonth])
var country = MAX('Table'[Country])
var category = MAX('Table'[Category])
var lastmonth =
CALCULATE(
MAX('Table'[YearMonth]),
FILTER(
ALL('Table'),
'Table'[YearMonth]<m&&
'Table'[Country]=country&&
'Table'[Category]=category
)
)
var lastmonthval =
CALCULATE(
SUM('Table'[Value]),
FILTER(
ALL('Table'),
'Table'[YearMonth]=lastmonth&&
'Table'[Country]=country&&
'Table'[Category]=category
)
)
var result =
DIVIDE(
SUM('Table'[Value])-lastmonthval,
lastmonthval
)
return
IF(
ISINSCOPE('Table'[Category]),
result
)``````

``````Delta Average =
var _avg =
CALCULATE(
AVERAGE('Table'[Value]),
FILTER(
ALL('Table'),
[Country]=MAX('Table'[Country])&&
[Category]=MAX('Table'[Category])
)
)
var result =
DIVIDE(
SUM('Table'[Value]),
_avg
)-1
return
IF(
ISINSCOPE('Table'[Category]),
result
)``````

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.  Helper II

Hi, @v-alq-msft thanks for your help. It looks good but i have antoher "problem" 😕 I dont have the categories in one column. The categories in my file are different measures from different tables. So i cant test your solution in my test file 😕

Maybe i upload my test file, then you can see the dataset. But which way is the best to pusblish my test file?

Thanks in advance 🙂  Community Support

Hi, @Wedding55

You may share the pbix file with OneDrive for busibess. Do mask sensitive data before uploading. Thanks.

Best Regards

Allan  Helper II

Hi, @v-alq-msft

is it also possible with onedrive basic? So i can create a private acc. because my official account for onedrive for business in our company does not allow external access.

Best Regards  Helper II

@amitchandak  thanks for your reply. The problem is that with this measures i only calculate the Sales Amount and i can not show the delta between last month for the sales amount, number of orders and number of customers in one column (like in the screenshot). Do you have any other ideas?  Super User IV

@Wedding55 , if you have date table you can

try measure like

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!  Helper II Here i try it in Paint to show you what im searching for.  ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group! #### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks Top Solution Authors
Top Kudoed Authors
Users online (7,907)