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

MattAllington's avatar
MattAllington
Community Champion
10 years ago

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

 

=DIVIDE(numerator,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.