Sum across two linked tables

[Saarek]

I'm still relatively new to PowerBI, what I want to do should be simple, but I just can't seem to make it work.

I have two tables that are linked by the Column name of APE, think of APE as the equivalent of Months, so AP01 would be the equivalent of Jan, etc.

I wish to work out what the total is likely to be once a set percentage figure is removed. The simple logic for this would be Sum(Value) - Sum(Value * Percentage). If we had £100 and the percentage was .10 the result would be £90.

No matter what I seem to do though the figures just don't make sense.

My Current code is pasted below:

Predicted = CALCULATE(SUM(Prophecy[ActualPrem]),Prophecy[Type] = "RN",Prophecy[Status] = "Upcomming") / CALCULATE(SUM(Prophecy[ActualPrem]),Prophecy[Type] = "RN",Prophecy[Status] = "Upcomming") * (SUM(Lapse_Predict[AP_Mean]))

I have a pivot table in my report that contains "APE", "Value" and the "Predicted" value and it's just nonsense.

My head says that Power BI should see that the APE columns are linked and so the sum should work, but £68,708,528 is coming back with a predicted of £1.95 against a target of .145678 which is just not even close to right.

[PaulDBrown · Accepted solution]

@Saarek 

Ok, in that case you don't actually need to create a dimension table since you already have a one-to-many relationship. In your visuals, slicer and filters, use the APE field from the Lapse_predict table (ie. 'Lapse_Predict[APE])

As regards your measure:

Predicted = CALCULATE(SUM(Prophecy[ActualPrem]),Prophecy[Type] = "RN",Prophecy[Status] = "Upcomming") / CALCULATE(SUM(Prophecy[ActualPrem]),Prophecy[Type] = "RN",Prophecy[Status] = "Upcomming") * (SUM(Lapse_Predict[AP_Mean]))

Try:
Predicted = 
VAR v1 = CALCULATE(SUM(Prophecy[ActualPrem]),
                   FILTER(Prophecy,
                     Prophecy[Type] = "RN" &&
                      Prophecy [Status] = "Upcoming")
VAR v2 = SUM(Lapse_Predict[AP_Mean])
VAR v3 = SUMX(Lapse_Predict, v1 * v2)
RETURN
DIVIDE( v1, v3)

[PaulDBrown]

@Saarek 

Can you post a screenshot of your model?

(My guess is that you have linked both tables directly using a many-to-many relationship).

You need to create a dimension table (a.k.a. "bridge" table) either in Power Query or using DAX which must contain unique values for the APE field.

For example, if you APE fileds in your tables were Table1[APE] and Table2[APE], you can create this Dimension table for APE using (in the ribbon, select Modeling and "New Table" and type in the equivalent code):
Dimension APE Table = 
VAR t1 = VALUES(Table1[APE])
VAR t2 = VALUES(Table2[APE])
RETURN
DISTINCT(UNION(t1, t2))

(Change the colour coded fields to the fileds in your model)

Next delete the relationship between your fact tables, and create a new relationship between the newly created DImensio APE Table and each of your fact tables by linking the Dimension APE Table field to the corresponding APE fields in both tables.

Now use this DImension APE Table field in your measures, filters, slicers etc..

BTW, you should do the same for all the fields common to both tables 

[Saarek]

Thanks for your reply, there is only one common field as it's the secondary table is just a simply lookup table.

There are only 12 rows in the lookup tables going from AP01 - AP12.

Here are screenshots.

[PaulDBrown · Accepted solution]

@Saarek 

Ok, in that case you don't actually need to create a dimension table since you already have a one-to-many relationship. In your visuals, slicer and filters, use the APE field from the Lapse_predict table (ie. 'Lapse_Predict[APE])

As regards your measure:

Predicted = CALCULATE(SUM(Prophecy[ActualPrem]),Prophecy[Type] = "RN",Prophecy[Status] = "Upcomming") / CALCULATE(SUM(Prophecy[ActualPrem]),Prophecy[Type] = "RN",Prophecy[Status] = "Upcomming") * (SUM(Lapse_Predict[AP_Mean]))

Try:
Predicted = 
VAR v1 = CALCULATE(SUM(Prophecy[ActualPrem]),
                   FILTER(Prophecy,
                     Prophecy[Type] = "RN" &&
                      Prophecy [Status] = "Upcoming")
VAR v2 = SUM(Lapse_Predict[AP_Mean])
VAR v3 = SUMX(Lapse_Predict, v1 * v2)
RETURN
DIVIDE( v1, v3)

[Saarek]

Thanks for the suggestion, but your code returns blank result for all entries.

[PaulDBrown]

@Saarek 

Any chance you can share some dummy data?

[Saarek]

Have attached the excel example here:https://file.re/2020/10/15/example-data/ 
Data split into two tabs which should make sense.

[PaulDBrown]

@Saarek

Apologies since it seems I didn't write a condition correctly: I wrote "Upcoming" instead of "upcomming" (according to the sample data you posted the link to).

So change the value to "upcomming" in the FILTER and see if it works.

That is:

Predicted = 
VAR v1 = CALCULATE(SUM(Prophecy[ActualPrem]),
                   FILTER(Prophecy,
                     Prophecy[Type] = "RN" &&
                      Prophecy [Status] = "upcomming")
VAR v2 = SUM(Lapse_Predict[AP_Mean])
VAR v3 = SUMX(Lapse_Predict, v1 * v2)
RETURN
DIVIDE( v1, v3)

[Saarek]

Hi There, it's no longer blank, but is giving numbers similar to the ones my own formula generated.

[PaulDBrown]

@Saarek 

So what does your expected result look like?

Your calculation is basically:

1) calculate the sum of values when two conditions are met

2) multiply the same value * the Mean value

3) Divide 1 by 2

[Saarek]

@PaulDBrown  Sorry, me again. Both your formula and mine work, but not on totals and for the life of me I cannot work out why.

Row by Row the calculation is spot on, I manually checked, but the totals for your or my manual calculation just make no sense.

Here is a photo, doesn't matter if it is in a chart, table, KPI, the total's are always wrong.

[PaulDBrown · Accepted solution]

@Saarek 

Based on the sample data you provided, I can get the correct totals by breaking down the measure (I've tried different combinations using VAR and SUMMARIZE functions to no avail).

 The first two (SUMs) are simple sums of the corresponding columns.

Then:

SUM IF "RN" and "upcomming" = SUMX(
                  FILTER(Prophecy,
                     Prophecy[Type] = "RN" &&
                      Prophecy [Status] = "Upcomming"),
                     [SUM of ActualPrem])

Mean * SUM if "RN" and "upcomming" = 
SUMX(Lapse_Predict, [SUM of Mean]*[SUM IF "RN" and "upcomming"])

and finally

Predicted Measure = 
[SUM IF "RN" and "upcomming"]- [Mean * SUM if "RN" and "upcomming"]

[Saarek]

Thanks you so much @PaulDBrown , I'd never have worked that way out by myself!

[Saarek]

If we consider the AP10 result, the Upcomming value is £913,699.87. The AP_Mean value for AP10 is 0.2102306.

Sum(913,699.87) - Sum(913,699.87 * 0.2102306) = £722,401.97

That's the requirement.

**Edit, which answers my own quesiton doesn't it. I just realised that my original DAX Formula had a / as opposed to a -

Too many revisions, although your advice of using the other Tables APE value in the pivot table was part of the solution.

Thank you so much for your help.