Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Wedding55
Helper II
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).

Wedding55_1-1606494465412.png

 

 

 

 

 

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
v-alq-msft
Community Support
Community Support

Hi, @Wedding55 

 

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

Table:

c1.png

 

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:

c2.png

 

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

Wedding55
Helper II
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? 

amitchandak
Super User
Super User

@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))

Wedding55
Helper II
Helper II

Wedding55_0-1606495644382.png

 

Here i try it in Paint to show you what im searching for.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.