DAX- Incorrect sum of a column based on slicer selection

[Anonymous]

Hi Community,

Following is a snapshot of my PBI report. I am trying to create a measure based on selection of cost centre. The cost centre selected appears on any of the two columns - 'Primary cost centre' & 'Other cost centres'

Here is the measure :

Total Position Hours =

Var Selectval = values(cost[description])

return

Calculate(sum(Temp[position Hours]),filter(emp, emp[Primary cost centre] in Selectval || emp[Other cost centre] in Seletval))

Please note: 'Temp' is a table created from emp table using SUMMARIZE.

Temp = Summarize(emp, position code, position name, emp_code)

This measure is giving incorrect result. The expected result in the above example in snapshot is (22.50+38+24+38) =122.50, but the measure results in 146.50. So, its calculating ' position hours' for Assistant account emp codes -002 & 444 twice (24+24), instead of once.

Please suggest a solution.Any help is much appreciated.

Thanks,

Meena

[sturlaws · Accepted solution]

Hi @Anonymous,

if you provide a sample dataset or sample pbix-file, it is far easier for anyone to help you.

You could try to replace the sum()-part of you expression with something like this:

sumx(SUMMARIZE('Temp';'Temp'[Position code];'Temp'[Position hours]);'Temp'[Position hours])

[sturlaws · Accepted solution]

Hi @Anonymous,

if you provide a sample dataset or sample pbix-file, it is far easier for anyone to help you.

You could try to replace the sum()-part of you expression with something like this:

sumx(SUMMARIZE('Temp';'Temp'[Position code];'Temp'[Position hours]);'Temp'[Position hours])

[Anonymous · Accepted solution]

Thanks @sturlaws for your response. It would work for someone in similar situation.

Actually, I managed to resolve the issue by modifying a relationship amongst the tables.