cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
YGoe Frequent Visitor
Frequent Visitor

new measure leading to error

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.

1 ACCEPTED SOLUTION

Accepted Solutions
dedelman_clng New Contributor
New Contributor

Re: new measure leading to error

@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

View solution in original post

8 REPLIES 8
Dog Established Member
Established Member

Re: new measure leading to error

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 Frequent Visitor
Frequent Visitor

Re: new measure leading to error

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 Established Member
Established Member

Re: new measure leading to error

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 Frequent Visitor
Frequent Visitor

Re: new measure leading to error

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 Established Member
Established Member

Re: new measure leading to error

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 Frequent Visitor
Frequent Visitor

Re: new measure leading to error

@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 New Contributor
New Contributor

Re: new measure leading to error

@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

View solution in original post

YGoe Frequent Visitor
Frequent Visitor

Re: new measure leading to error

@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 🙂

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors