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.
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
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 )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 🙂
You may share the pbix file with OneDrive for busibess. Do mask sensitive data before uploading. Thanks.
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.
@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?
@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))
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!