desktop

[Anonymous]

Hi,

i am facing an issue with dax, i have written a if condition to create a new column. need a support to fix this problem asap.

i am explaining the issue with dummy data, i have two columns like A, B and have calculated column as C. 

Condition is:- C= If( A!=0 then B/A else 0). Please find thebelow image to know better 

Dax query with resultsIf you see the above pic i am unable to get exact value for ovarall like 25/27 as 92.6%(this is the result i am expecting) but i am getting result like 404.0% as sum of 'C' column. if i take average it getting like 57.7% as 404.0/7=57.7%taking sumtaking avg of C

So kindly help me on this. Its much appricitated. Thanks in advance. 

[Wickin · Accepted solution]

Try creating the following measure (not a column):

C =
DIVIDE ( SUM ( dummy[B] ), SUM ( dummy[A] ), 0 )

[Wickin · Accepted solution]

Try creating the following measure (not a column):

C =
DIVIDE ( SUM ( dummy[B] ), SUM ( dummy[A] ), 0 )

[Anonymous]

Thanks a lot Wickin for your quick response.

It's working but i tried this but i am expecting the result like row wise like below

want to see the result like this including 92.6% in Red color box expecting all the rows with % and in the end also the same like 

A   B  C

---------------

7    6  85.7%

4    3  75.0%

9    6  66.7%

5    3  60.0%

2    1  50.0%

0    6  00.0%

-----------------

27   25   92.6%

Thanks in advance.

[jthomson]

---

@Anonymous wrote:

Thanks a lot Wickin for your quick response.

It's working but i tried this but i am expecting the result like row wise like below

---

Our formulas do that. You might need to turn off summarisation/turn on values with no data in your visual.

[Anonymous]

Thanks a lot JThomson and Wickin for the resolution for my issue. It worked well.

[Anonymous]

Hello JThomson, while using "C = DIVIDE ( SUM ( dummy[B] ), SUM ( dummy[A] ), 0 )" formula i am getting the below result.

with sum on both the columns A, B.

if i apply don't summarise, getting below result

if i use the "C = IF(dummy[A] <> 0, (dummy[B]/dummy[A]), 0)" formula. getting the below results  but not able to get as 92.6% as totals under C column. If its fixed. then i am fine..As per the client requiremnt,  if A is not equals to 0 then B/A else 0.

Have almost 30 million records are there thats why explainin my senorio with dummy data.

i have taken the Table as visual and if i get the value 92.6% in first ed box with all the rows. Then my met my requirment.

Thanks

[Anonymous]

Hi JThomson/Wickin ...kindly help on the below to sort out. Thanks in advance

[jthomson · Accepted solution]

You had the result you wanted in your previous post...

[Wickin]

Not sure what result you're seeing currently.

Could you please add your PBIX file?

[Anonymous]

I am unable to attach here cn you pls share your email id so that i will share the .pbix file with you.

[jthomson]

See what c = divide (b, a, 0) does, may need to make it sum(a) and sum (b)

Would also help if you use a descriptive topic title in the future...