Really Basic BI - linking two tables and failing miserably to get the right totals

[ruthhacche]

I have a 'data' table of 190 orders that have a [DATE YearMonth] field - all 190 lines = 201908 , and a [PICK Labels] field for number of labels (only 4 lines > 0 and totalling 41 labels).
I have a 'PICKRATES' table of 60 lines of rates per month -the line for [month] = 201908 in this table has a rate of £0.28. (All other months are £0.28 also).
I have linked the tables in a many to one relationship both directions on the month - double check that if I show the month from each table there are still 190 lines (not 190 * 60) for all the orders and only 201908 is showing in the month field from both tables.
If I display the rate it is £0.28 on each line. All good.

My SQL head adds a measure as below for PICK LableCost. This works when the data is filtered for just the 4 lines that have an order the total is - 41 x £0.28 = £11.48. But if I remove the filter and show the 190 lines the total is £688.80 which is 41 x £0.28 x 60 (ie multiplied up for all months in the rate table not just 201908).

I have tried all sorts of other links and combinations and cannot get anywhere near correct. So clearly I am taking fundamentally the wrong approach, and/or the linking is wrong and/or the measure is wrong. Any help or pointers gratefully received. All I want is all 190 records coming into play and the total cost for the labels across the 190 orders to be £11.48. Then I can move on to doing the complicated bits (!!!).

 

[JustynaMK]

Hi,

I think the issue is with your measure.

I tried recreating your two tables along with many-to-one relationship, here's the table that I'm working with (notice the incorrect Total value):

Your measure is properly calculating PICK LabelCost on a row-per-row basis, including Total LabelCost when a single month filter is applied:

As you already noticed, the issue starts when the Month filter is removed and you present data for all months.
For the Total row, your DAX formula is following these calculation instructions:

1. sum all PICK Labels,
2. sum all PICK LabelRate,
3. multiply these two values.

However, what we would expect the Total to do is:

1. multiply PICK Labels * PICK LabelRate,
2. sum these values together.

This DAX measure should do the trick -

PICK LabelCost = 
    IF(HASONEFILTER(data[Month]),
        SUM(data[PICK Labels]) * SUM(PICKRATES[PICK LabelRate]),
        SUMX(data, data[PICK Labels] * RELATED(PICKRATES[PICK LabelRate])))

It is checking if a filter has been applied to the Month column. If yes, continue with the standard calculation, if not - it means we are now in the Total column without any Month filters applied. In such case SUMX will iterate through the whole data table, multiply two values, and then sum all these values together.

I hope this will solve your issue.

 

[ruthhacche]

Wow. you have Done so much work. Thanks you millions. I need clear light of day to absorb. But thank you. So much.

 

[JustynaMK]

no problem at all.
Take care & I'll be waiting for the test results tomorrow.

 

[ruthhacche]

It surely does work. So the key bit being this.

SUMX(data, data[PICK Labels] * RELATED(PICKRATES[PICK LabelRate])

But this is pretty basic use of data in tables. Solutions in excel and SQL are so simple in comparison. Why so much faff to do such a simple thing is BI? Could I have approached it differently, with an additional column or a different table linking?

 

[JustynaMK]

Agreed, it's not the most user-friendly/intiutive tool, especially if you come from an Excel/SQL background, and I suspect there's nothing else you could do to improve your report.
The good news is that it will get easier with time! Just to provide you with one of my favorite quotes: DAX is simple but not easy.

I really recommend reading this PowerBI Community thread - here's the key statement:
The issue surrounds using Measures in Table visualizations with a Total row. The complaint is that the "Total" row is "wrong" for the measure. Technically, the total row is correct for the measure, it's just not what most people expect. What people expect is for the "Total" to display the sum of the values in the column. Measures do not do this. Measures respect the context of the Total row and is calculated within that context. Therefore, a Measure used in a column in a table visualization will likely have an unexpected value in the Total column.

This might be changed in the future if PowerBI community will upvote this idea (see link and also link), but it doesn't seem to be very popular as both links currently have only a few votes.

 

[ruthhacche]

Indeed. Most of it is working now but I have problems on the shipping label cost. In the data table for this account the first order has 3 tracking IDs [ORDER Tracking] so three lines in the data table. I am working out the number of labels [PICK ShipLabels] = DISTINCTCOUNT( (data[ORDER Tracking]) ) which works as below as it is displayed. It is also picking up the correct label rate. But using the calc that has worked elsewhere it is not getting the cost - and indeed I cannot even see what logic it is using to get 15 and 2 as the multiple. Where there is only one order for an account and one tracking ID (only one line in the table for the account ) it works but as soon as we have more than one order per account or more than one tracking ID per order (ie when using the DISTINCTCOUNT) it is not. So is there any alternative structure for this?

 

[JustynaMK]

I suspect the issue lies with the calculated column:

[PICK ShipLabels] = DISTINCTCOUNT( (data[ORDER Tracking]) )

Your Data table will show this value multiple times (5 times for order 1905.2783.02 and 2 times for 1908.4305.02), e.g.:

Now when you are calculating PICK ShipLabelCost measure, SUMX formula will:

1. iterate through the whole Data table ("X" is an iterator),
2. it will pick up the first Order number (e.g. 1905),
3. pick up the first row's distinct value (=3), multiply it by 0.11
4. it will repeat the same process four more times and aggregate (SUM) the final result (=3*0.11*5).
5. The same process will happen for the second order no (1908).

It's a little bit difficult to explain but I hope my explanation isn't too confusing....!

Anyways, here are two potential solutions you can try:

A. Sneaky (ugly) way -

PICK ShipLabelCost = 
   SELECTEDVALUE(data[PickShipLabels]) * SELECTEDVALUE(PICKRATES[PICK ShipLabelRate])

B. A more "proper" way -

PICK ShipLabelCost = 
   CALCULATE(
       DISTINCTCOUNT(data[ORDER Tracking]) * SUM(PICKRATES[PICK ShipLabelRate]),
       FILTER(data, data[ORDER No] = SELECTEDVALUE(data[ORDER No]))
   )

 

[ruthhacche]

Thank you for this - your explanations help me understand this clearly. Interestingly I had solved this but by reverting to type (SQL thinking). I had added a column into the data table being 1/count of the tracking IDs and then I had applied the rate to that - seriously ugly. I am not yet instinctively seeing the relational and filter aspects to these functions but I guess that will come. At the moment you are my best friend. Have a lovely day.

 

[JustynaMK]

I'm glad to hear you are progressing nicely. It will get easier with time, promise! If you mastered SQL / Excel then DAX won't be too challenging, it's really just a few bits that are different (e.g. DAX doesn't really understand the concept of columns/rows, it understands whole tables & filters applied to it).

Have a great weekend & stay safe.