Calculate Difference in a Matrix

DAX DAX Calculations
1

Hello dear powerbi lovers,

today I have a challenge for you that I can not finally solve.

Currently, a matrix displays the total amounts between two companies.

image

However, I would like to have only the difference amounts displayed.
Since the underlying FactTable is several million rows large, I would like to calculate this using a Measure / Virtual Tables.

image
image472×531 6.47 KB

Unfortunately, all my efforts so far have been unsuccessful.
My last approach to create this via a virtual table fails because I was not able to assign the amounts for Amount 2 using the columns Company 2 and IC Partner Company 2.

image
image875×92 4.05 KB

Since this report is used very often by the users, I am very concerned about a good performance.

Do you have any ideas? Solution? for the mentioned challenge?

You will find an Excel table with sample data as well as the current PowerBI file in the attachment.

Thanks in advance

Danilo
FactTable_TestIC.xlsx (11.1 KB)
Test_IC_Calculation.pbix (55.1 KB)

2

@d.mesch I looked at you pbix file. You have the same company values as in your lookup tables (160, 180, etc) I guess you created these tables for slicers? You can only use TREATAS for tables that you want to use as a virtual relationship but in this case I don’t know why you would.

I would do a simple DAX of sum of your IC company values and do the same for your company values then a DAX measures for the difference.

Totals IC Partner = sum( FactTable[IC Partner Company] )
Totals Company Partner = sum( dim_Company[Company] )
SUM of Amount = sum( FactTable[Amount])
Diff Company = [Totals IC Partner] - [Totals Company Partner]

Dates should be the only relationship to your fact table that I can see.

Unless I misunderstood your question.

Paul

image

image

Might use SELECTED VALUE with SWITCH

3

Here is something you could possibly use:

Difference between Companies= 
VAR AllYears =
    CALCULATE (
        CONCATENATEX (
            VALUES ( 'Table1'[Year] ),
            'Table1'[Year] ,
            ","
        )
    )
VAR FirstYear =
    RIGHT ( AllYears, 4 )
VAR LastYear =
    LEFT ( AllYears, 4 )
VAR FirstYearAmount =
    CALCULATE (
        [Amount],
        'Table1'[Year] =  FirstYear 
    )
VAR LastYearAmount =
    CALCULATE (
        [Amount],
        'Table1'[Year] = LastYear 
    )
RETURN    FirstYearAmount – LastYearAmount
4

Or use a calculated column

5

Hello @d.mesch, just following up if the response above help you solve your inquiry?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

6

Hello @d.mesch, it’s been a while since we got a response from you. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

7

Hi Paul,

sorry for answering late. But I really needed some holidays.

I think we have misunderstood in the solution.

As you can see in the image, I like to have the difference per Period, Account, … off the amount between the company and the IC company. So for example the dif. between company 160 and IC Company 180 for January is 0, for April is 100 and for the full year is 4400.

image

image
image362×506 20.1 KB

But instead of showing the Total amounts, I need to only show the difference in the table, so 0 for January, 100 for April, 4400 as the sum, and so on.

May you or anybody else can help?

Thx in advance.
Danilo

8

Hello Paul,
when thinking through it again, the solution came. Derived from your 2nd post, I have developed the following.

   Difference between Companies = 

VAR Company =
   SELECTEDVALUE(dim_Company[Company])

VAR ICCompany =
    SELECTEDVALUE(dim_ICPartnerCompany[IC Partner Company])

VAR Company2 = ICCompany

VAR ICCompany2 = Company

VAR FirstAmount =
    CALCULATE (
    [SUM of Amount],
    dim_Company[Company] = Company,
    dim_ICPartnerCompany[IC Partner Company] = ICCompany
    )

VAR SecondAmount =
    CALCULATE (
    [SUM of Amount],
    dim_Company[Company] = Company2,
    dim_ICPartnerCompany[IC Partner Company] = ICCompany2
    )

RETURN FirstAmount - SecondAmount

The code works very well for my purposes.

So thanks again for your help.

I wish you and all the hard workers at EnterpriseDNA a successful 2022.

Greetings
Danilo

1 Like