Forum Discussion

Saarek's avatar
Saarek
Icon for Helper III rankHelper III
5 years ago
Solved

Sum across two linked tables

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.
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    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's avatar
    PaulDBrown
    5 years ago

    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"]

     

13 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Saarek
      Icon for Helper III rankHelper III
      5 years ago

      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's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        5 years ago

        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)