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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
curious_111
New Member

Creating order amount and shipped amount Measures for Matrix visual

I can't use calculated columns to multiply two columns "Order Quantity" and "Sales Price" initially to get the overall Order Amount. 

 

The Order Amount and Shipped Amount use different dates, and I need to get them both into a Matrix Visual

curious_111_0-1710534261347.png

Initially I use SumX('table', 'table'[column1] * 'table' [column2] to get the Order Amount. But I need to specify the date somehow with regard to "Shipping Amount" in order to get the measure necessary to implement the second row into the Matrix. As both Order Amount & Shipping Amount has same formula but according to the orderdate or shippingdate the sum value changes according to month and year. When I introduce both order amount and shipping amount in a table visual along with one of the date column they both are showing same values either order amount values or shipping amount values according to the date type I am considering.

curious_111_1-1710534547277.png

Order amount and shippedamount, orderdate, shippingdate all 4 are in same table. But, I do have another table called INVOICE where I have below columns.

curious_111_2-1710534745470.png

In this table I tried to create shippingamount column by just using sum(Invoice Amount) which is giving me similar values to order amount when observed with invoice month

curious_111_3-1710534893617.png

I hope I am explaining this right. Any help would be appreciated. 

 

Thank you
NS

 

1 ACCEPTED SOLUTION

It's a good start

- Products is a dimension table and should control Orders in a 1:* relationship. Looks like you have duplicates in the Products table though, you may want to clean that up

- I would argue that Orders and Invoices are independent facts, don't join them directly. Unless you can guarantee that you only ever have one invoice for one order.  Or refute my assumption

- Add a calendar table to your data model.  You always, always need a claendar table.  Link it to the order date (active) and the ship date (inactive)

View solution in original post

7 REPLIES 7
curious_111
New Member

This is my data model

curious_111_0-1710658272062.png

 

It's a good start

- Products is a dimension table and should control Orders in a 1:* relationship. Looks like you have duplicates in the Products table though, you may want to clean that up

- I would argue that Orders and Invoices are independent facts, don't join them directly. Unless you can guarantee that you only ever have one invoice for one order.  Or refute my assumption

- Add a calendar table to your data model.  You always, always need a claendar table.  Link it to the order date (active) and the ship date (inactive)

Thank you, It worked.

I need to create a bar graph using the shipped amount and ordered amount. which should be like this.

curious_111_0-1710832534343.png

But when I am trying to achieve this I got graphs like below

curious_111_1-1710832616226.png

 

curious_111_2-1710832710061.png

I am not sure exactly how can I solve this. My date_table code is

 

Date_Table =
VAR _Table=
ADDCOLUMNS(
    CALENDAR(DATE(2013,1,24),DATE(2014,2,31)),
    "Consecutive Week Number",WEEKNUM([Date],2)
    + 52
        * ( YEAR ( [Date] ) - YEAR (DATE(2013,1,24) ) ),
    "Week Number & Year","W" & WEEKNUM([Date]) & " " & YEAR([Date]),
    "WeekYearNumber",YEAR([Date]) & 100 + WEEKNUM([Date]),
    "MonthShortYear",FORMAT([Date],"mmm-YYYY")
)
RETURN
_Table

your first screenshot is too small. Can't see what you need.

curious_111_0-1710918279496.png

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

lbendlin
Super User
Super User

This is a standard USERELATIONSHIP pattern.  Please show your data model.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors