Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Simple model not doable?

We have a very simple model with Partners, PartnerSales, PartnerRebates (see below). There is also an MyCal = CALENDARAUTO() table for filtering calendar - related data.

 

What we need to see is total sales vs. total rebates grouped by partner and month. Problem is, there seems to be no way to create such pivot table.

If we do a SUMMARIZE('Partners'), then it is impossible to include sales and rebates data into the pivot table. If we do a SUMMARIZE() over either PartnerSales or PartnerRebates, then not all data is included in the resulting pivot table - specifically, the data from "other" table (PartnerRebates in case of summarizing over PartnerSales) is only included IF there is a full match for the row. In the example below, only data related to 12 february is included:

 

 

Is there any way to override this limitation and include *all* rows from the other table, even if there is no matching dates?


Code for pivots is:

SumOnSales = SUMMARIZE('PartnerSales'; Partners[CompanyName]; MyCal[Date]; "SumSales"; SUM(PartnerSales[SamesAmount]); "SumRebates"; SUM(PartnerRebates[RebateAmount]))

SumTableOnPartners = SUMMARIZE(
     PartnerRebates;
     Partners[CompanyName];
     MyCal[Date];
     "SumSales"; SUM(PartnerSales[SamesAmount]);
     "SumRebates"; SUM(PartnerRebates[RebateAmount])
     )

Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous whatever measure you are using add zero to it and it will force the measure to return zero value instead of blank() which is the reason that rows are not showing up, so for example measure will look like this

     

    My Measure = SUM ( Table[Column] ) + 0
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This example is about creating a pivot table, it does not involve any measures

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This has nothing to do with data transformation when importing into the model. Maybe it was not clear enough in the question.

       

      The question is purely being UNable to include data from other table when using calculated pivot tables.