new measure leading to error

[YGoe]

Dear All, 
 
I am trying to create a new measure with the following dax script:  
 
PAR = CALCULATE(sum('home original_data'[data_point]),left('home original_data'[acc_code],1)<>"9") /  CALCULATE(sum('home original_data'[data_point]), 'home original_data'[acc_code] = 95001001)
 
this above measure returns infinity for NaN. However when I replace  CALCULATE(sum('home original_data'[data_point]), 'home original_data'[acc_code] = 95001001) by the filtered value, I get the correct answer. 
 
Does any know what is going on?? any advice would be greatly appreciated. 
 
Many thanks.

[Dog]

Hi, 

there doesn't seem to be any issue with what you've written. 

if it was me, I'd look at testing them individually first to make sure you are happy with the two sets of results you are returning. 

also - you may find laying out the statement like the following for ease of reading. Try the Divide statement as well. 

PAR:=

var Res1 = CALCULATE(sum('home original_data'[data_point]),left('home original_data'[acc_code],1)<>"9")

var Res2 = CALCULATE(sum('home original_data'[data_point]), 'home original_data'[acc_code] = 95001001)

RETURN 

DIVIDE(Res1, Res2, 0)

if the measure is laid out like this you can just amend the return statement to return Res1 so you can check the output and then change again to Res2 to check the output. 

RETURN 

Res1

I hope this helps a little. 

Dog

[YGoe]

Hi Dog, 

Thanks for taking the time to look into this. 

I have tried your proposed solution and it still doesn't work . 

the first strip of code returns a list of values and works as intended. 

the second should only return 1 value which it does.

What I am trying to achieve is to divide every record returned by 

CALCULATE(sum('home original_data'[data_point]),left('home original_data'[acc_code],1)<>"9") by the 1 value returned by CALCULATE(sum('home original_data'[data_point]), 'home original_data'[acc_code] = 95001001).

for example, if the first half returns 10, 20, 30 and the second half returns 10 then the answer should be 1,2,3

my apologies is I wasn't clear enough. 

thanks

[Dog]

Hi @YGoe

no problem, out of interest how are you trying to visualise this? 

a measure will always return a single cell so I imagine that this is going to aggregated in some fashion to give you 10, 20, 30? 

if it's being aggregated then is the problem, when you are seeing the output down to the 2nd value not always returning the same value? 

or am I way off 🙂

Dog

[YGoe]

Hi @Dog

I am testing the measure in a table in PowerBI and there is no aggregation. 

I believe the division is not working because the denominator only returns 1 cell and the division only occurs where the denominator has a value. 

I haven't found a way to divide a whole bunch of values by 1 given cell like in excel. 

for example, divide eveything by cell A1.

Row   Value   division    answer

a1        10        a1/a1        1

b1        20       b1/a1        2

c1        30        c1/a1        3

[Dog]

you should be able to I think.

using the below as a an example.

if value is being returned from your measure

value:=CALCULATE(sum('home original_data'[data_point]),left('home original_data'[acc_code],1)<>"9")

it's being filtered at a row level on column "Row" 

so your division answer should come from this measure. 

answer:=

var res = CALCULATE(sum('home original_data'[data_point]),left('home original_data'[acc_code],1)<>"9")

var divvalue = CALCULATE(sum('home original_data'[data_point]),left('home original_data'[acc_code],1)<>"9", ALL('Table'[Row]))

return

divide(res, divvalue, 0)

does that make sense? 

Dog

[YGoe]

@Dog I don't really understand the logic here. If I'm right, the all function remove filters from a given table. 

If I do this then I will not be able to isolate and get the desired denominator. 

in the above example the list of values is collected using: CALCULATE(sum('home original_data'[data_point]),left('home original_data'[acc_code],1)<>"9")

the above script would return values like 10, 20, 30

the denominator, is found using: CALCULATE(sum('home original_data'[data_point]), 'home original_data'[acc_code] = 95001001)

this returns the desired denominator 10

I am sorry if I got this wrong 🙂 

[dedelman_clng · Accepted solution]

@Dog, @YGoe

I think you guys were on the right track.  For the denominator you need to remove all of the filters and then reapply the right one.  Without first removing the filters, the left([acc code],1) <> 9 remains, and therefore acc_code cannot equal 95001001 and you get 0:

Denominator =
CALCULATE (
    SUM ( 'home original_data'[data_point] ),
    ALL ( 'home original_data' ),
    'home original_data'[acc_code] = 95001001
)

Hope this helps,

David

[YGoe]

@dedelman_clng, @Dog,

Thank you very much for your help to both of you. I have been trying to sort this out for days!! 

very much appreciated 🙂