Sum over value and show sum of not matched values

[DominikR]

Sorry if my title is a little bit confusing.

So my Problem is the following. I have this data modell

data model

In Supervisor_1 every Employee has only one Supervisor in Supervisor_2 a Employee can have multiple supervisors. Not every Employee has a supervisor.

Now I want to sum the sum_of_sales over the supervisors. This is the output:

output

So in Supervisor 1  I get the expected output. Because 4 Sales were made from employees without a supervisor.

But in Supervisor 2 this row is missing and I dont quite understand why. It must be because a employee can have multiple supervisors in this case.

Why is this? I need to display also the sales without a supervisor.

Thanks!

[DominikR]

The Problem is also described here

https://docs.microsoft.com/en-us/power-bi/desktop-many-to-many-relationships

I want to be able to display the "empty" row. Does anybody know how to do this?

[LivioLanzo]

@DominikR

that link refers to a difference scenario. The equivalent on your side would be if you had the Sale supervisor stored within the sale table and you'd create a relationship between Sales[Supervisor] and Supervisor_2[Supervisor] without creating a Supervisor dimension

[DominikR]

Actually I don't think so. In the beginning I had only the Sales and the Supervisor table. I then created the employee table because of the m to n relationship between the sales and supervisor table with the employee_id. And this is excatly what is described in the articel. I want to group by Supervisor ("State" in the articel) and sum over the sales (also "Sales" in the articel).

Now I want to be able to see a blank row covering mismatched rows --> this is when there is a null value for employee_id. I want to know the number of sales for those.  

[v-frfei-msft]

Hi @DominikR,

Could you please share your sample data here?

Reagrds,

Frank

[DominikR]

Hi Frank,

of course!

Here is a dropboy link to the pbix file

https://www.dropbox.com/s/g7zdojr1p3hrmir/Test.pbix?dl=0

Thank you!

[v-frfei-msft · Accepted solution]

Hi @DominikR,

We can change the relationship between Supervisor_unique_emp_id and Supervisor. Then create measures to workaround to get the sum of valules not matched.

Create the measures as below.

Measure = CALCULATE(SUM(Sales[Sum_of_Sales]))

NuLL = CALCULATE(SUM(Sales[Sum_of_Sales]))-SUMX(ALLSELECTED(Supervisor[Supervisor]),[Measure])

Regards,

Frank

[v-frfei-msft]

Hi @DominikR,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

[LivioLanzo]

Hello @DominikR !

There is no way to know this from your current data model. All the supervisors assigned to a Client will have as Sale amount equal to that of this Client ( plus that of their other clients of course ). You should've probably stored the Supervisor in the Sales table