REQ help; to get the value from column1 based on column2 when row in column1 is zero

[Invisibleman]

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;

• 2 columns; Sales Amount, Cost Amount. But 1 of them is then always showing zero.
• 1 column with Sales Order numbers. This number is the same in the row for the sales amount and also for the cost amount. So on this I need to get the data from
• 1 Column with the Sales district. Which only has the value when the row is for the sales, when it’s for the cost, it will show an empty cell.

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

[rautaniket0077 · Accepted solution]

Hi @Invisibleman,
Please follow these below steps.

1) beneath the  modeling tab select new table and paste the below dax code

code -- 

Cost_table =

    SUMMARIZECOLUMNS(

        Sales[Posting Date],

        Sales[Del.Dt],

        Sales[Qty],

        Sales[Sales Order],

        Sales[Order Item],

        "total_cost",

        SUM(Sales[SalesCost(Local Currency)])

    )

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.

[Invisibleman · Accepted solution]

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.

Cost_table =

    SUMMARIZECOLUMNS(

        Sales[Sales Order],

        Sales[Order Item],

        Sales[Invoice#],

        "total_cost",

        SUM(Sales[SalesCost(Local Currency)])

    )

This give me the correct result as I also double checked with the excel. 

But I wouldn't found this out, without the help.

So thanks again.

Regards,

Hans

[rautaniket0077 · Accepted solution]

Hi @Invisibleman,
Please follow these below steps.

1) beneath the  modeling tab select new table and paste the below dax code

code -- 

Cost_table =

    SUMMARIZECOLUMNS(

        Sales[Posting Date],

        Sales[Del.Dt],

        Sales[Qty],

        Sales[Sales Order],

        Sales[Order Item],

        "total_cost",

        SUM(Sales[SalesCost(Local Currency)])

    )

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.

[Invisibleman · Accepted solution]

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.

Cost_table =

    SUMMARIZECOLUMNS(

        Sales[Sales Order],

        Sales[Order Item],

        Sales[Invoice#],

        "total_cost",

        SUM(Sales[SalesCost(Local Currency)])

    )

This give me the correct result as I also double checked with the excel. 

But I wouldn't found this out, without the help.

So thanks again.

Regards,

Hans

[Invisibleman]

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

[rautaniket0077]

Hi @Invisibleman ,
please provide the screenshot of error versus what needed.

[Invisibleman]

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

[Wilson_ · Accepted solution]

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:

• basic data modeling (in particular your use of many-to-many relationships and bi-directional filtering are non-standard and should only be used when necessary)
• having a good, robust date table to replace your MonthName table

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?)

[Invisibleman]

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

[Invisibleman]

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

[Wilson_]

Hi Hans,

Thanks for sharing. Unfortunately I cannot access your sharepoint. Can you upload them to your google drive or a dropbox or something?

[Wilson_]

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. 😄