Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Multiplication returning weird results.

Dear All, I am facing a strange issue that the multiplication,price * qty in the below screenshot, is not happening properly, I have created a calcualted column  as RevW/oTax = 'Report lines'[price...
Need Help
  • FrankAT's avatar
    FrankAT
    5 years ago

    Hi Anonymous ,

    you have a mismatch between using implicite measures and columns.

    All columns signed with a sigma are numerical data fields. If used in a visual, Power BI Desktop automatically uses an implicite aggregation, in your point Sum (see figure).

    Get rid of the automatic use of implicite measures:

    1. Select the data field.

    2. Tab Column tools > Don't summarize.

    3. The data field qty / price will be renamed to Sum of qty / Sum of price. Remove these fields from the values and insert them new. Now you get the expected values for RevW/oTax.

     

    figure 1

     

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

     

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Amit,
Thanks for your response. I have tried you suggestion but no luck.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    If you really have a real calculated column in your base table 'Report lines', then this measure 100% does what you want:

     

    [Your Measure] = SUM( 'Report lines'[RevW/oTax] )

     

    where 'Report lines'[RevW/oTax] is the field you say you have calculated. It's NOT a measure.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Daxer,
      Thank you for your suggestion but it is not working and it is resulting duplicates.

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        5 years ago

        Hi,

        The problem is the Shipping City and Country fields that you have dragged to the visual.  They are from the Report Order table and there is no relationship between Report Lines and Report Order tables.  You should create 2 additional dimension tables - one for shipping City and another for Country.  Create a relationship from the shipping City and Country column from Report Order and Report lines tables to the 2 new tables.  To your visual, drag Shipping Order and Country from the 2 new tables.  Once you do that, this simple measure will work

        Measure = SUM('Report lines'[RevW/oTax])
        Hope this helps.