Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
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 🙂
Hi, @Wedding55
You may share the pbix file with OneDrive for busibess. Do mask sensitive data before uploading. Thanks.
Best Regards
Allan
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
@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))
Here i try it in Paint to show you what im searching for.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |