Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
I have a problem with Power BI which I have no idea in how to solve. I hope that any of you may help me. Please notice that I am still in the beginning of the Power BI, but can manage some parts.
I want to have in Power BI a chart which I want to base, the sales & the cost, on the sales district. For this I have 1 report (called SalesOrders), but the sales amount and the cost amount are not in the same row, but have different rows.
Then I in the rows, or the sales amount or the cost amount.
In this report I have;
Now the question is; how can I use the sales Order Number to get the sales amount and also the cost amount, when I am selecting the Sales District in Power BI?
Any help would be very appreciated.
Thanks in advance,
Hans
Solved! Go to Solution.
Hi @Invisibleman,
Please follow these below steps.
1) beneath the modeling tab select new table and paste the below dax code
code --
2)Establish the relationship between the sales and new cost table based on Posting Date
3) use cost from cost table instead of sales table and i guess you will get your desired output.
please mark my answer as solution if it solves your issue.
Hello Rautaniket0077,
Thanks for the solution. However, it didn't give the correct result, but did help me a lot to get the correct values. I assume, maybe too much selections didn't match in the cost and also the sales, so I changed the formula to the selection I also did use in excel.
Hello Hans,
I've made a change to your pbix and re-uploaded it here. All I did was add a calculated column to your Sales table to populate the sales district with the correct sales district (based on the sales order number) if the sales district was blank. I then used that new column as the field in the relationship between the Sales and EUCFT's tables.
That should solve the question you came to the forums for. However, since you mentioned you're new to Power BI, I want to point out you would also benefit from looking into things like:
Check out the guys at SQLBI here; they were incredibly helpful for me as a complete beginner (and still are!).
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
Hi @Invisibleman,
Please follow these below steps.
1) beneath the modeling tab select new table and paste the below dax code
code --
2)Establish the relationship between the sales and new cost table based on Posting Date
3) use cost from cost table instead of sales table and i guess you will get your desired output.
please mark my answer as solution if it solves your issue.
Hello Rautaniket0077,
Thanks for the solution. However, it didn't give the correct result, but did help me a lot to get the correct values. I assume, maybe too much selections didn't match in the cost and also the sales, so I changed the formula to the selection I also did use in excel.
Hello Rautaniket0077,
I am very sorry, but I need to come back to this. As there is still something wrong. If I do your way or my way. When I don't select the sales district then indeed the amount seem to match. However, when I select the sales districts one by one, and count the cost amount, then I have a total of more then 10 times the actual cost. So it doesn't consider the sales district to be counted.
Regards,
Hans
Hello Wilson,
I'm sorry I only hve the OneDrive. But here are links from another OneDrive, I hope these will help.
Excel File and here the The PowerBI file
Regards,
Hans
Hello Hans,
I've made a change to your pbix and re-uploaded it here. All I did was add a calculated column to your Sales table to populate the sales district with the correct sales district (based on the sales order number) if the sales district was blank. I then used that new column as the field in the relationship between the Sales and EUCFT's tables.
That should solve the question you came to the forums for. However, since you mentioned you're new to Power BI, I want to point out you would also benefit from looking into things like:
Check out the guys at SQLBI here; they were incredibly helpful for me as a complete beginner (and still are!).
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
Hello Wilson,
Thanks for helping me out. Then I also know, some parts how I can do others, if needed. And also thanks for pointing me out the many-to-many relations. I will also keep this in mind.
But in general for now, I try bit by bit and then when I have some bits, I combine to one. And so on. So this many-2-many I will consider if there are other option I can use. Maybe the solution you now provided, may also be an option to do with others.
Thanks,
Hans
Hello Wilson,
Here you go the pbix and also a excel file, where I show what it currently is and what I need.
Test Pbix and the excel file Example Excel file
Beside this issue I also can't figur out how I can make the correct relationships between 2 different databases, so that when I select a date that both databases will be using the same selection.
Anyway thanks for the help,
Regards,
Hans
Hi Hans,
Thanks for sharing. Unfortunately I cannot access your sharepoint. Can you upload them to your google drive or a dropbox or something?
Hello Hans,
I'd be happy to try and help.
Can you please provide a pbix file? It might also help if you can re-create (in Excel or something) and share what the final solution you're envisioning is. That would probably be easier (at least for me) than having to parse your description of the data model. 😄
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |