Simple model not doable?

[Anonymous]

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

     )

[v-yuta-msft]

@Anonymous ,

Have you tried "Pivot" and "Unpivot" in query editor? You may also refer to the tutorial below:

https://www.youtube.com/watch?v=OYM26ZytimM

Regards,

Jimmy Tao

[Anonymous]

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.

[parry2k]

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

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