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

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's avatar
MattAllington
Community Champion
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

    hey MattAllington

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    sry MattAllington.

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

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

  • 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
      Community 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. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        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.