Calculated measure only shows grand total in matrix, regardless of slicer choices

[addaline]

I have read through the forums and various tutorials, but am not quite grasping this one in PBI Desktop.

I have a data table (Sales), with all sales information (Sales rep, Dates Sold, Per Sale Totals, etc)

I have a date measure (Date) with a breakdown of all the various combinations of dates (Day, Month, Year, YearMonth, Long Month Name, etc)

I have an invoice targets table (InvoiceTargets) with each Sales Rep and their monthly targets.

I have created a report with various calculated measures that appear to be behaving correctly when the fields are used in a Matrix visual, and filter with Slicer selection.

For example, I'm showing Sales Reps, their Monthly results for Actual Invoices, and the totals in the matrix add up correctly whether I show all months, or slice two or three months. It only shows the selected months' totals, as desired. The Actual Invoices measure is a simple SUM calculation against the Invoiced Amount column of the Sales Table.

However, when I try to use the measure Target Invoices, calculated against the Invoice Target column of the InvoiceTargets table, the matrix totals will only display the Grand Total for each Sales Rep, regardless of Slicer selections.

The Target Invoices measure is created as follows:

Target Invoices = 
CALCULATE(
    SUM( InvoiceTargets[Target] ),
    FILTER( ALLSELECTED( InvoiceTargets[YearMonthNumber] ), 
            CONTAINS( VALUES( 'Date'[YearMonthnumber] ), 'Date'[YearMonthnumber], InvoiceTargets[YearMonthNumber]  ) ),
    FILTER( ALLSELECTED( InvoiceTargets[SalesRep] ), 
            CONTAINS( VALUES( Sales[Agent] ), Sales[Agent], InvoiceTargets[SalesRep] ) )
)

Any suggestions on where I'm tripping up with this?

Thanks in advance,

Dion

[v-micsh-msft · Accepted solution]

Hi addaline,

Please check with the relationship of yoru tables.

I supsect there should be some part mis-configured or missing regarding the table relationship, sepcially for the InvoiceTargets table.

Create and manage relationships in Power BI Desktop

Regards

[v-micsh-msft · Accepted solution]

Hi addaline,

Please check with the relationship of yoru tables.

I supsect there should be some part mis-configured or missing regarding the table relationship, sepcially for the InvoiceTargets table.

Create and manage relationships in Power BI Desktop

Regards

[addaline]

Hi Michael,

I forgot to resolve that issue!

I have a relationship between my Sales table and my Date table, linking the "Completed Date" in the Sales table to the "Date" in the Date table.

But, I can not seem to create a relationship with the InvoiceTargets table.

Every combination I seem to try tells me that it can't be done due to "missing intermediate data".

(SalesRep = Agent)

I'm just not sure what I can add/change to any of the tables to ensure it gets the unique data it needs to create the link from InvoiceTargets table to (I'm assuming) Date table.

Thanks,

Dion

[addaline · Accepted solution]

Ah ha!

My slicer was pulling against the wrong table.

I initially had the Year slicer and Month slicer each going against the Sales table.

I changed them both to use the Date table, and my Invoice Targets started showing the correct total based on what was filtered by the slicer.

Now to clean up the Closing Rate % caclulation!

Thanks for the pointer that lead me around all my items again!

Dion