Rate Per Hour

[labuser1235]

Hi All,

I have two tables here with almost similar kind of data. However when I try to give relations there are two columns which needs to get matched for the end result. Which one to be taken as active and other one as inactive, either ways it goes wrong in the output table. PBIX LINK 

Please help.

Table1:

Date	Fruit Name	Type	Price Sold	Time taken to sold in min
1/31/2020	Orange	Type-2	7	68
1/31/2020	Apple	Type-3	8	34
1/31/2020	Grapes	Type-1	2	12
1/31/2020	Orange	Type-1	5	65
1/31/2020	Orange	Type-3	10	28
1/31/2020	Apple	Type-1	3	15
1/31/2020	Grapes	Type-2	4	95
1/31/2020	Grapes	Type-3	6	47
1/31/2020	Orange	Type-1	5	32
1/31/2020	Orange	Type-2	7	12
1/31/2020	Apple	Type-3	8	16
1/31/2020	Grapes	Type-1	2	24
1/30/2020	Orange	Type-1	5	3
1/30/2020	Orange	Type-3	10	28
1/30/2020	Apple	Type-1	3	34
1/30/2020	Grapes	Type-2	4	7
1/30/2020	Grapes	Type-3	6	17
1/30/2020	Orange	Type-1	5	30

Table2:

Fruit Name	Type	Price
Orange	Type-1	5
Orange	Type-2	7
Orange	Type-3	10
Apple	Type-1	3
Apple	Type-2	5
Apple	Type-3	8
Grapes	Type-1	2
Grapes	Type-2	4
Grapes	Type-3	6

Output Matrix Table: 

Fruit Name	Type	Price	Avg Time	Rate per hour
Orange	Type-1	$   5.00	32.5	$       9.23
Orange	Type-2	$   7.00	40	$    10.50
Orange	Type-3	$ 10.00	28	$    21.43
Apple	Type-1	$   3.00	24.5	$       7.35
Apple	Type-2	$   5.00	0
Apple	Type-3	$   8.00	25	$    19.20
Grapes	Type-1	$   2.00	18	$       6.67
Grapes	Type-2	$   4.00	51	$       4.71
Grapes	Type-3	$   6.00	32	$    11.25

[amitchandak · Accepted solution]

Please find the calc.

https://www.dropbox.com/s/jgzxsxjlcz3ixmk/RatePerHour.pbix?dl=0

Per Hour Rate = DIVIDE(Table2[Price],Table2[Avg Total])*60

[Anonymous · Accepted solution]

Hi,

To get the results, actually you don't need any relationship between the two tables. Delete the relationships.

Then add the following calculated columns to your Table 2:

AvgTime = AVERAGEX(
            FILTER('Demo data',
                    AND('Demo data'[Fruit Name]='Fruit Price'[Fruit Name],
                                'Demo data'[Type]='Fruit Price'[Type])
            ),'Demo data'[Time taken to sold in min])

Rate Per Hour = DIVIDE('Fruit Price'[Price],'Fruit Price'[AvgTime],0)*60

This two calculated columns in Table 2 will give you the desire out except for "Apple". Because in one table it is spelt as "Apple" and in the other table it is spelt as "Apple " - with an extra space. Once you correct that spellings in your source table, it will work fine.

[vivran22 · Accepted solution]

Hello @labuser1235

There are various ways to approach this. Personally, I try to avoid many-to-many relationship (if it is required) in Power BI.

Alternate way is to create filter tables with unique values and then create One-to-Many relationship:

I have used DAX to create these two tables:

ftFruit = DISTINCT(dtFruits[Fruit Name])

ftFruit = DISTINCT(dtFruits[Fruit Name])

Created another DAX for Rate/hour

Rate per Hour = DIVIDE(SUM(dtSales[Price Sold]),SUM(dtSales[Time taken to sold in min]))*60

Following is the output:

You may find the solution pbix file here

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

[labuser1235]

Thank you so much @vivran22 . Didn't think to have seperate tables even this method is working. 

[Anonymous · Accepted solution]

Hi,

To get the results, actually you don't need any relationship between the two tables. Delete the relationships.

Then add the following calculated columns to your Table 2:

AvgTime = AVERAGEX(
            FILTER('Demo data',
                    AND('Demo data'[Fruit Name]='Fruit Price'[Fruit Name],
                                'Demo data'[Type]='Fruit Price'[Type])
            ),'Demo data'[Time taken to sold in min])

Rate Per Hour = DIVIDE('Fruit Price'[Price],'Fruit Price'[AvgTime],0)*60

This two calculated columns in Table 2 will give you the desire out except for "Apple". Because in one table it is spelt as "Apple" and in the other table it is spelt as "Apple " - with an extra space. Once you correct that spellings in your source table, it will work fine.

[labuser1235]

Thank you so much, it worked like charm 🙂

[amitchandak · Accepted solution]

Please find the calc.

https://www.dropbox.com/s/jgzxsxjlcz3ixmk/RatePerHour.pbix?dl=0

Per Hour Rate = DIVIDE(Table2[Price],Table2[Avg Total])*60

[labuser1235]

Awesome worked like charm. Thank you so much 🙂