Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

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 M...
Need Help
  • MattAllington's avatar
    MattAllington
    10 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
10 years ago

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.