Count number of same values and multiply with value from another table

[Anonymous]

Hi all,

I'm new to Power BI and still struggling a bit.

I have two tables, one shows me the orders and the other one shows me the prices for the different products. Now I want to get the revenue per product (per year) by counting how many times a product was orderes from the list that shows me the orders and then multiplying this with the respective price. 

I tried to create a chart that shows me the different products and as value I've tried to create a Quick Measure multiplying the amount of sold products with the price, but this is not possible.

Can anyone help me here?

Cheers!

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

First, make sure that there is a one to many relationship between the two tables based on.

Then create a calculate column in the order table to fill in the price of the product table

_price = RELATED ( Prices[Price] )

Sample:

To create a measure to calculate the Revenue:

Revenue =
CALCULATE (
    COUNTROWS (
        FILTER ( 'Orders', 'Orders'[Product] <= MAX ( 'Orders'[Product] ) )
    )
        * SELECTEDVALUE ( 'Orders'[_price] )
)

Result:

Please refer to the attachment below for details

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link whether it is what you are looking for.

I created a simple sample by myself, and I tried to include the information you want and measures you need.

All measures are in the sample pbix file.

https://www.dropbox.com/s/bhbgwtmv6j8yddj/Picture1.png?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi Jihwan Kim,

thank you, that's exactly what I am looking for! I only see the dropbox link to the picture, where can I find the pbix file?

[Jihwan_Kim]

Hi, @Anonymous 

Sorry, here is the correct link

https://www.dropbox.com/s/pkj9pi2n9vpvkvx/friedl.pbix?dl=0

[Anonymous]

So if I understand it right I first need to sort the sales after days? 

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

Sorry, I did not understand your question well.

In my sample, dates are just the date each sale happened.

If your data has a different structure, then the data model needs to be created differently and measures have to be created differently. 

[Anonymous]

Hi Jihwan Kim,

here's some sample data to show you the structure of my data model. 

https://www.dropbox.com/s/aubykp93ktr8guk/Sample_Data.pbix?dl=0

[Jihwan_Kim]

Hi, @Anonymous 

Please check the link down below.

Dim-Calendar table and Dim-Contry table are created.

At the moment, the relationships between DimCalendar Table and Order Table are all inactive relationships.

If you want to activate one of the relationships, you can use userelationship function in the new dax measures.

All measures are in the sample pbix file.

https://www.dropbox.com/s/axauiibjwlji136/Sample_Data.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi Jihwan Kim,

thank you for the file. I think I wasn't clear in my expressions. 

With Order no I mean the unique number that is created for every order. So I want to calculate the different orders by counting the different order numbers, I think you understood Order no as amount of orders. 

[Jihwan_Kim]

Hi, @Anonymous 

Sorry for my misunderstanding.

I confused the Order number with how many numbers of orders.

I am familiar with seeing something like Order ID No., and I was not careful enough and confused.

I think the link down below seems like matching your expected outcome that is presented before by you.

You can check the changed measures in the sample pbix file.

https://www.dropbox.com/s/axauiibjwlji136/Sample_Data.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Sure, sorry! 

This is my table with the orders:

This is my table with the prices:

Now I want to create a chart that shows me the revenue by multiplying the amount of sold product with the price for the product, so that it looks like that in the end:

[ryan_mayu]

@Anonymous 

Please see the attachment below

[Anonymous]

Thank you!!

[ryan_mayu]

@Anonymous 

could you please provide the sample data and the expected output?