measure for multiply 2 columns by years

[mauroanelli]

hi all,

i have a table (hr_analytic_timesheet) with 3 columns

Employees | Ore | Date

and another table (Dipendenti Costo/H) with

Employess |  Costo H | Date 

i need to get total cost by employees (Ore * hourly cost)

as Costo H change every year i need to get a measure that multiply the hours worked for the hourly cost of the same year

i try with 

Tot Costo/h = CALCULATE(SUM(hr_analytic_timesheet[Ore])*SUM('Dipendenti Costo/H'[Costo H]);ALLSELECTED('Calendar'[Anno]))

it work within the matrix visual but total and subtotal are all wrong and i don't know why

any suggestion?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[v-xicai]

Hi @mauroanelli ,

You can try the following measure. What is the relationships between the table hr_analytic_timesheet ,Dipendenti Costo/H and Calendar look like ? Or could you please share your sample data or desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Tot Costo/h = CALCULATE(SUM(hr_analytic_timesheet'[Ore]),FILTER(hr_analytic_timesheet, hr_analytic_timesheet[Employees ]=MAX(hr_analytic_timesheet[Employees ])&&YEAR(hr_analytic_timesheet[Date])=YEAR(MAX(hr_analytic_timesheet[Date])))) * CALCULATE(SUM('Dipendenti Costo/H'[Costo H]),FILTER('Dipendenti Costo/H', 'Dipendenti Costo/H'[Employees ]=MAX('Dipendenti Costo/H'[Employees ])&&YEAR('Dipendenti Costo/H'[Date])=YEAR(MAX('Dipendenti Costo/H'[Date]))))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mauroanelli]

there's a relation between 

hr_analytic_timesheet [employees] and Dipendenti Costo/H [employees]

hr_analytic_timesheet [Data] ,Dipendenti Costo/H [Data] and Calendar[Data]

with your measure the subtotal in row and column get the last value and not the total

[v-xicai]

Hi  @mauroanelli ,
 
Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards
Amy

[v-xicai]

Hi @mauroanelli ,

This maybe a measure totals problem that total row data join in calculation. You can try to create new measure [Tot Costo/h_New] beside [Tot Costo/h] like DAX below, assuming you need to show field [employees] and this new measure [Tot Costo/h_New] in your Matrix Visual.

Tot Costo/h_New=

VAR _table = SUMMARIZE('hr_analytic_timesheet ',[employees],"_Value",[Tot Costo/h])

RETURN

IF(HASONEVALUE([employees]),[Tot Costo/h],SUMX(_table,[_Value]))

You can refer to the similar case: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907 .

If the issue still exist, could you please share your sample data or desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.