Table values are getting repeated

[taumirza]

Hello,

I got 4 columns in my 'Table' all of them are custom columns created using query editor

1. Gross Sales

2. COGS

3. Gross Margin     (Gross Sales - COGS)

4. Gross Margin %    (Gross Margin / [Gross Sales)

Problem is as long as i put only first 3 column in a table for a specific dimension, say customers, the values are good but the moment i put the 4th calculation(Gross Margin %) the values are getting repeated.

For Ex:  If i get 10 unique customers for customer column when i use first 3 measures

            I am gettin 10 customers twice in the 'Table' for the customer column when i place 4th measure(Gross Margin %) in the table. 

did i miss any kind of grouping property for the column while calculating Percentage values...?

Please help me with this. Thanks in advance. 

[MattAllington]

You really shouldn't be creating these as columns, even from the query editor.  Your last 2 should be written as measures. Read about it here. http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

Your problem is strange. What is the join between the customer table and this data table?

[taumirza]

Thanks for the reply @MattAllington.

I created it as measure using  sum funtion .

But i got NaN and Infinity values i tried if condition there 

if (divisor = 0,0,division expression)

but the thing is , it is taking very long cus um working in direct query mode.

and dont know y i got an extra empty row with 0.00% value for the calculated column and all other column values as empty at the start of the table just after the headers...!!(Please look at the image i attached) 

is there any alternative there to replace Infinity and Nan values in table 

[MattAllington]

There is an inbuilt DIVIDE function that handles divide by zero errors. 

=DIVIDE(numerator,denominator)

[taumirza]

Thank u.

Can u please help me out with this too..?

Gross Sales is there and i wanna do following calculation 

Cus   Gross sales   new_column(Gross Sales / Total Gross Sales)

  1       100                100/300

  2       200                200/300

Total   300    

How um i suppose to use total of gross sales column in measure calculation..??           

[MattAllington · Accepted solution]

You should write a measure that produces the 300 regardless of the value in the rows

denominator =calculate(sum(table[gross sales]),all(table[cus]))

then write a measure 

=sum(table[gross sales])/denominator

[taumirza]

Hi @MattAllington.

A . Can u please tell what is the difference between 

1. 'Add Custom Column'  (at Edit Queries > Add Custom Column)

   &

2. 'New Column'               (Power BI Desktop Report view)

B. What language does this 1 uses? we are unable to use any aggregation function like 'SUM' etc.. neither we can use 'Calculate' function which is a part of DAX.

can you please elaborate about these two whenever you feel free..!!

I have gone through this artice in which i can clearly say whatis the difference between 'Calulated Column' and 'Calculated Measure', but I am unable get difference between 1 and 2(talked earlier). 

Thanks in advance.

[MattAllington]

Theres are 2 distinct and different technologies rolled into Power BI

1 is Power Query.  It uses a language officially called Power Query Formula Language, developed from "M". I call it PQL (like SQL). Power Query is an ETL tool used to shape data before loading to Power Pivot

2 is power pivot. It is a reporting data base that uses DAX as a language. SUM is an example

you question makes me think is is a good blog post topic. I will post back here once it is done. 

[taumirza]

Thanks for your reply @MattAllington

can you please tell how we gonna differentiate 

1. New Column           (Reoprt view) where we use new column instead of new measure

2. New Measure          (Report view) where we use new measure instead of new column

Actually I have experience with 'New Measures' which are giving me good results but I didn't go for 'New Column' in my reports yet, because I started Power BI a month ago.

It is ok even if u want me to refer a blog, but please help me out with this.

Thanks in advance. 

[MattAllington]

I always refer to my blog for this question. http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

generally it is ok to create new columns as long as you know "why" you are doing it over a measure. Also, better to push the column work back to power query or to the source. 

[taumirza]

sry @MattAllington.

I did a mistake giving arguments to 'all' funtion

but got it now thank you so much for ur help.

[taumirza]

hey @MattAllington

i tried ur way but its giving me only 1.00 value in every row...