Forum Discussion
Table values are getting repeated
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.
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
11 Replies
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?
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
There is an inbuilt DIVIDE function that handles divide by zero errors.
=DIVIDE(numerator,denominator)