Multiplication returning weird results.

[Anonymous]

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]*'Report lines'[qty].
I have also created measure as Rew w/o Tax = SUMX('Report lines',CALCULATE(SUM('Report lines'[price])*SUM('Report lines'[qty]))), which is also not returning correct values. The source is Azure datalake Gen 1, Kindly help me how to fix this issue.Below is the link to PBI file which contain two pages one with calculated column and another with measure. Thank you.

https://drive.google.com/file/d/1KlvykW9zhFHE3dCiW_2ExfDbiV9Kbe2o/view?usp=sharing

[FrankAT · Accepted solution]

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]

Hi FrankAt,

Thanks alot. It worked.

[Anonymous]

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

[Anonymous]

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]

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

[Ashish_Mathur]

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.

[amitchandak]

@Anonymous , Multiplication should be done at line level try like

SUMX('Report lines','Report lines'[price]*'Report lines'[qty])