how to display values in matrix without affecting another columns value from different table

[gauri]

Hi,

I have matrix visual , where i have months in my column sections , sales person and types in row section, Actual and targets in the values sections. only the targets are from different table and rest everything is coming from same table. Now for a salesperson "riya" there is a target present for type "A" in table "Target qty" but there are no actual values for "riya" for the same type "A" from the Actuals table. Also there is no row present in actuals stating 0.

How can i pull Target values for a sales person from one table even if there are no actuals for it in another table

Thanks,

gauri

[v-yingjl · Accepted solution]

Hi @gauri ,

To get the correct result in matrix, you need to extract a separate type table first:

Type Table = DISTINCT('Target table'[Type])

Create relationship between the type table and the target table:

Create a measure like this to calculate the actual values:

Actual =
CALCULATE (
    SUM ( 'Actual table'[Sales] ),
    FILTER (
        ALL ( 'Actual table' ),
        'Actual table'[Type] IN DISTINCT ( 'Type Table'[Type] )
    )
) + 0

Now the matrix can show the correct value:

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@gauri , Not very clear. You need to have a common salesperson table.  and analyze both against it

refer:  https://www.youtube.com/watch?v=Bkf35Roman8

[gauri]

@amitchandak  sales person column is present in both the tables but when it comes to type that every sales person has , that i snot present in actual table. 

For eg : Salesperson Riya is present in both the tables "Actuals" and "target Qty". she has types under her as "A", "B". 

There are target values present for riya both the types "A" & "B" in the "Target qty" table

but, Actual qty from "Actuals" table is only present for TYPE "A", it doesnt have any row for type "B".

Yet i want to display my targets for all the types whether they are present in actuals table or not

[amitchandak]

@gauri , You need a common table for Types too. Type should not get filtered if either target or sales are nonblank

Otherwise, you can use this option on the type

[gauri]

@amitchandak  i did try earlier show item with no data, but it did not work in this scenario.

Also i tried with bridge table but its not giving me the target value, giving blank.