2
$\begingroup$

I want to learn PowerBI so I downloaded it connected PowerBI to a MySQL database that uses this test data set:

http://learn.evermight.net/johnlai/demostore.sql

That data set has the tables invoice,invoice_item,customer,address,product.

I want to use PowerBI to give me a table of data that yields the same result as this query:

SELECT customer_id, CONCAT(first_name,last_name),
    (SELECT SUM(qty*unit_cost)
    FROM invoice_item WHERE invoice_id IN
        (SELECT invoice_id FROM invoice WHERE invoice.customer_id = customer.customer_id))
    AS PurchAmt
FROM customer
GROUP BY customer_id
ORDER BY PurchAmt DESC;

+-------------+------------------------------+-------------+
| customer_id | CONCAT(first_name,last_name) | PurchAmt    |
+-------------+------------------------------+-------------+
|          10 | YuriPreb                     | 13082458.70 |
|           7 | SylvaiBelange                | 13031101.01 |
|           9 | YmnuaKrish                   | 13004262.97 |
|           8 | AmnaChowdury                 | 12843937.12 |
|           3 | FlorenceIel                  | 12786294.27 |
|          11 | GordonBiyas                  | 12722387.44 |
|           5 | SabrinaFabo                  | 12675754.67 |
|           4 | AlishaMml                    | 12662484.31 |
|           1 | JayBobbin                    | 12537697.14 |
|          12 | SimulaUmmea                  | 12376986.77 |
|           2 | JayisonGam                   | 12313863.63 |
|           6 | FabriceLabe                  | 12176486.31 |
+-------------+------------------------------+-------------+

After connecting to the database with PowerBI, I defined two new columns in PowerBI:

Cost = 'demostore invoice_item'[qty]*'demostore invoice_item'[unit_cost]
FullName = CONCATENATE('demostore customer'[first_name],'demostore customer'[last_name])

Then I clicked the checkbox for fields customer.customer_id,customer.FullName,invoice_item.Cost and this is what I got:

enter image description here

Why are the results in PowerBI different from my SQL query? For example, MySQL says Fabrice has 12176486.31 but PowerBI says 11286529.84 ?


UPDATE

I noticed that if I checkmark instead the following fields invoice.customer_id,invoice_item.Cost, and I do not summarize the invoice.customer_id as shown in this image here:

enter image description here

Then Customer 6 (which is Fabrice) shows the same amount as my MySQL query. What's going on?

$\endgroup$
3
  • 2
    $\begingroup$ I’m voting to close this question because the questions belongs to other SE site $\endgroup$
    – 10xAI
    Jun 16 '20 at 1:17
  • $\begingroup$ @RoshanJha which SE site would you recommend? $\endgroup$
    – John
    Jun 16 '20 at 14:33
  • 1
    $\begingroup$ Stackoverflow with powerbi tag e.g. stackoverflow.com/questions/tagged/powerbi $\endgroup$
    – 10xAI
    Jun 17 '20 at 9:02

Browse other questions tagged or ask your own question.