Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I need help on creating 3 measures, namely TotaQty, TotalAmount, Amount/Qty.
There are 4 tables, all are connected with one to many in one by one cascade. I attach a sample .pbix file, it is a sample version:
https://drive.google.com/file/d/1LKp2GURuu3KDaPvfCc9Cuxo4n8iPY5ct/view?usp=sharing
in my original data, the measure TotalAmount never loads. I think its becuase it fetches all the rows from Table-3 which are not related to filtered FreightID.
As I add the TotalAmount measure to the visual, it fetches rows showing InvoiceID which are not related with original FreightID or LNo. I think I need to adjust these measure to work well.
My intended output is as below:
FreightID | LNo | InvoiceID | Qty | TotalQty | TotalAmount | Amount/Qty |
1 | 1 | 1 | 200 | 2200 | 9000 | 4.091 |
1 | 1 | 1 | 400 | 2200 | 9000 | 4.091 |
1 | 1 | 2 | 200 | 2200 | 9000 | 4.091 |
1 | 1 | 2 | 600 | 2200 | 9000 | 4.091 |
1 | 2 | 3 | 300 | 2200 | 9000 | 4.091 |
1 | 2 | 4 | 500 | 2200 | 9000 | 4.091 |
Basically measures should ignore LNo and InvoiceID fields and fetch totals only based on FreightID.
Hope I provided adequate information to resolve it.
Hi @mahenkj2 ,
I've been researching this for a long time and couldn't come up with your expected results, but close. Your initial problem was that the relationship was not set up both ways. The problem now is that once you traverse the table, you get all the row data.
An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-tianyich-msft ,
Thank you for your time.
I saw the attachement but result is not what I am looking for, I see below result:
whereas in TotalAmount I wanted to see 9000 and in TotalQty =2200, I believe basically except FreightID, all columns in conext to go off.
Can I achieve that?
P.S. I depend on DAX very little, because just by optimum model design and Power Query, basic DAX is fine, but this time it looks like I need to dig into it or may be I am overcomplicating it.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
12 | |
9 | |
9 | |
9 |
User | Count |
---|---|
21 | |
14 | |
14 | |
13 | |
13 |