Totals in calculating weekly pay measure

[Tumbleweed]

My friends,

I'm calculating weekly hours and pay. Totals for hours measure are correct. However, I'm having difficult time with the pay. No matter what I do it doesn't get fixed. I tried summarize, hasonevalue, if functions to no avail.

Maybe you can help me to correct it?

https://drive.google.com/file/d/13ietQWO6sakcoyIdVVLanIWRuHvoLhuA/view?usp=sharing

[TomMartens · Accepted solution]

Hey @Tumbleweed 

try this measure instead:

Tom = 
SUMX(
    'Hours Per Week 1'
    , 'Hours Per Week 1'[ot1] * 'Hours Per Week 1'[OT Pay]
)

This allows creating this matrix visual:

Using the table iterator function SUMX ensures that the sum of the product of quantity and price is calculated. Otherwise, the product of sums will be calculated.

Hopefully, this provides what you are looking for.

Regards,

Tom

[Tumbleweed]

Hi Tom, It did work!!! Thank you so much! 

I have one more question if you don't mind: I need to show hour length in a slicer. I used the following DAX

that doesn't have any affect on the matrix. How I can create interactive measure/CC for the slicer? Thank you

Weekly OT Length =

SWITCH(

TRUE(),

'Table'[Measure Weekly OT Hours]< 2, "< 2 Hours",

'Table'[Measure Weekly OT Hours] >2, ">= 2 Hours")

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file with the correct measures already written in there.

[Tumbleweed]

Thank you

[Ashish_Mathur]

Hi,

There are formulas there which i do not agree with.  The hours per week 1 table is a calculated table and i do not see the reason why that should be a calculated table.  The OT1 column does not need a CALCULATE() function.  A lot of work to be done here.  May be you should wait for someone else to contribute who can help you with the way the tables are already there in your existing model.

[Tumbleweed]

Hi,

Thank you so much for your feedback. I do appreciate it.