cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
metin
Frequent Visitor

Create a comparison column using 3 tables

Dear All,

 

I am working on a report that I need your help and ideas. I have 3 tables that I am working on direct query mode to create report.

 

These 3 tables looks like simply as below in terms of columns,

 

Table 1 - Orders

Order Number - Product Code - Order Qty

 

Table 2 - Inventory

Product Code - Quantity Available

 

Table 3 - In Transit

Product Code - Quantity In Transit

 

I want create a comparison column in table 1 for each row showing if Order Qty - Qty Available - Qty In Transit is above 0 or not.

 

I have tried some of the ideas here but most of them were using Calculate function which does not work with direct query mode.

 

Looking forward to hearing your ideas.

 

Thanks.

7 REPLIES 7
metin
Frequent Visitor

There @v-easonf-msft,

It is not fully solved. I have been able to create a measure to show the gap between "order, inventory and GIT" however I can't convert it to a another measure or column using IF fucntion.

First I have calculated each tables value seperately with below measure.

Total By Material Number (INV) = CALCULATE(SUM(Inventory[Quantity Available]),GROUPBY(Inventory,Inventory[Product Code]))
Then I had another measure like this to show the gap.
Measure = Total By Material Number (Order) - Total By Material Number (INV) - Total By Material Number (GIT)
Up to here, it is working very well. I could create table as I would like.
My problem continues as follows. I would like to create an executive summary (pie chart or summary table) showing the percentage of orders (per value and quantity).
Therefore I tried to create a new measure with some IFs to convert the final measure into some text values like (if measure is below zero it will say "Not Available", or if it is above 0 and below 10, "Critical", and if above 10 "Available".
However this final measure with IFs is not working (not sure why) in the visuals. It is showing everything in the same bucket.
This is the problem I have at the moment.

Hi , @metin 

Pbix files in live conect mode are not suitable for sharing. It is recommended to paste  the simplified tables' data here,  then please  briefly introduce the relationship between  tables and your expected results.You also can upload a simplified version  in import mode to Onedrive and share the link here.

 

Best Regards,
Community Support Team _ Eason

Hi, @metin 

Can share more details for testing?

Perhaps your measure lacks the corresponding context for filtering in the current visual object. If so, you may need to use the “summarize“ function to create a temporary table for encapsulation before using the measure.

 

Best Regards,
Community Support Team _ Eason

There @v-easonf-msft

I have created a sample data set but not sure how I can share it with you.

The measure I am using in each table (Inventory, Order and GIT) simply looks like below;

Total By Material Number (INV) = CALCULATE (SUM (Inventory [Quantity Available]), GROUPBY (Inventory, Inventory [Product Code]))

Then I had another measure like this to show the gap.
Measure = Total By Material Number (Order) - Total By Material Number (INV) - Total By Material Number (GIT)
I can see end result of this formula when I put in table type of visual.
However I want to create a couple pie chart type of visuals which would show the percentage of products which have excess or minus stock. Above measures doesn't help to create such a visual.
Let me know if you need further details.
Thanks.
v-easonf-msft
Community Support
Community Support

Hi, @metin 

Could you please tell me whether your problem has been solved?
For now, there is no content of description in the thread. If you still need help, please share more details to us.

 

Best Regards,
Community Support Team _ Eason

metin
Frequent Visitor

Hi @v-easonf-msft 

 

Thanks for the suggestion. I wasn't aware of this of function. I will work with this for some time.

 

My first impression is that it works fine with small datasets. However in my dataset, as you may guess product codes are repeated in several rows in all tables (different warehouse locations, orders and multiple shipments for in transit goods). Therefore sometimes the result of above formula sometimes gave inaccurate results.

 

I feel like, first I should have a measure to sum in each table per product code and then apply a formula using this measure. SELECTEDVALUE function didn't seem to work with this way.

v-easonf-msft
Community Support
Community Support

Hi, @metin 

Have  you tried function "selectvalue"?

Measure = SELECTEDVALUE(Orders[Order Qty])-SELECTEDVALUE('In Transit'[Quantity In Transit])-SELECTEDVALUE(Inventory[Quantity Available])

87.png
Best Regards,
Community Support Team _ Eason


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.