cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
1001
Resolver II
Resolver II

Using the Related Function

Hi, from the image below, I have a dimension table (Group Map table) acting as a Fact table to connect two other dimension tables. My problem is this: 

I need to write DAX that says SUMX(Deliveries All, Deliveries All [Delivered] - Supplier, Supplier [Forecast]) 

Using the Related function, how am I able to achieve this to bring this answer into a Matrix table?

The reason I am asking this, is the ABS function does not provide an ABS column total (a Power BI bug). So I need to write a table formula to calculate this. Thanks. 

Related Function.jpg

 

  

1 ACCEPTED SOLUTION

Hi Gao,  Many thanks for your time and reply but I found the solution today.
Our Supplier Forecast table misaligned to our internal Delivery table. I was able to match them using the query editor by:

1) Within the Delivery table, use the Group function to group the table by Date, Shop and Product ID, SUM-ing the Delivered totals. 

2) I then duplictaed these columns and merged them into a Unique ID column called Delivery ID

3) I then duplicated and merged the same three columns in the Forcast table to the same Unique ID so the two tables could join. 

4) Using a left outer join, I then merged the Forecast Table into the Delivery table only keeping the product decription and forecast values from the Forecast table. The newly created unique ID columns joined the data perfectly. 

5) this will now allow me to write SUM and SUMX measures to create ABS values and totals.

So far, everything's talking to each other nicely.  Using display cards in the reports, I can now display proper ABS Totals for the Delivery and Forecast differences, something the Matrix table wasn't computing. 

The Matrix table was taking the Variance total and simply converting it to an ABS value, not SUMX-ing the product values as an ABS and SUMing those. 

That exercise was a head-bender. Now I know how to join tables from two different companies to compare numbers related to each other.  

 

Much appreciate your effort Gao. Cheers.

View solution in original post

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

Hi @1001 ,

Not sure, please try:

Column = 
VAR _VALUE = 'Deliveries_All'[Active]
VAR _RESULT = 
    CALCULATE (
        SUM ( Deliveries_All[Delivered] ),
        FILTER(ALL('Deliveries_All'),
        'Deliveries_All'[Active] = _VALUE
        )
    )
    - 
    CALCULATE (
        SUM ( Supplier[Forecast] ),
        FILTER(ALL('Supplier'),
        'Supplier'[Active] = _VALUE
        )
    )
RETURN
_RESULT

vcgaomsft_0-1668664112139.png

or:

Measure = 
VAR _VALUE = MAX('Deliveries_All'[Active])
VAR _RESULT = 
SUMX (
    VALUES ( Deliveries_All[Active] ),
    CALCULATE (
        SUM ( Deliveries_All[Delivered] ),
        'Deliveries_All'[Active] = _VALUE
    )
    - 
    CALCULATE (
        SUM ( Supplier[Forecast] ),
        'Supplier'[Active] = _VALUE
    )
)
RETURN
_RESULT

vcgaomsft_1-1668664143917.png

If I have misunderstood your needs, please feel free to contact me.

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Hi Gao,  Many thanks for your time and reply but I found the solution today.
Our Supplier Forecast table misaligned to our internal Delivery table. I was able to match them using the query editor by:

1) Within the Delivery table, use the Group function to group the table by Date, Shop and Product ID, SUM-ing the Delivered totals. 

2) I then duplictaed these columns and merged them into a Unique ID column called Delivery ID

3) I then duplicated and merged the same three columns in the Forcast table to the same Unique ID so the two tables could join. 

4) Using a left outer join, I then merged the Forecast Table into the Delivery table only keeping the product decription and forecast values from the Forecast table. The newly created unique ID columns joined the data perfectly. 

5) this will now allow me to write SUM and SUMX measures to create ABS values and totals.

So far, everything's talking to each other nicely.  Using display cards in the reports, I can now display proper ABS Totals for the Delivery and Forecast differences, something the Matrix table wasn't computing. 

The Matrix table was taking the Variance total and simply converting it to an ABS value, not SUMX-ing the product values as an ABS and SUMing those. 

That exercise was a head-bender. Now I know how to join tables from two different companies to compare numbers related to each other.  

 

Much appreciate your effort Gao. Cheers.

Helpful resources

Announcements
T-Shirt Design Challenge 2023

Power BI T-Shirt Design Challenge 2023

Submit your creative T-shirt design ideas starting March 7 through March 21, 2023.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors