Rate Per Hour

Please find the calc.

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

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

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.

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/