topic Re: Table values are getting repeated in Desktop

Table values are getting repeated

Anonymous — Sat, 16 Jul 2016 05:28:15 GMT

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.

Re: Table values are getting repeated

MattAllington — Sat, 16 Jul 2016 05:50:39 GMT

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?

Re: Table values are getting repeated

Anonymous — Sat, 16 Jul 2016 06:50:00 GMT

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

Re: Table values are getting repeated

MattAllington — Sat, 16 Jul 2016 06:52:22 GMT

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

=DIVIDE(numerator,denominator)

Re: Table values are getting repeated

Anonymous — Sat, 16 Jul 2016 07:15:08 GMT

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..??

Re: Table values are getting repeated

MattAllington — Sat, 16 Jul 2016 07:20:46 GMT

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

Re: Table values are getting repeated

Anonymous — Sat, 16 Jul 2016 08:04:29 GMT

hey MattAllington

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

Re: Table values are getting repeated

Anonymous — Sat, 16 Jul 2016 08:51:28 GMT

sry MattAllington.

I did a mistake giving arguments to 'all' funtion:smileymad:

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

Re: Table values are getting repeated

Anonymous — Wed, 03 Aug 2016 12:02:25 GMT

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.

Re: Table values are getting repeated

MattAllington — Wed, 03 Aug 2016 12:10:29 GMT

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.

Re: Table values are getting repeated

Anonymous — Wed, 03 Aug 2016 12:43:06 GMT

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.

Re: Table values are getting repeated

MattAllington — Wed, 03 Aug 2016 21:28:50 GMT

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.