Visual with incomplete information - Relationships

[Andvil]

Hello community,

I created relationships between tables and I want to export the visual information as xls file. In this example, I assign "employees" to some "customers", but not all customers have an employe assigned. I realized that my visual only shows the sales that have been assigned and it does not consider the sales without an employee, and instead of showing it blank, it just does not include it.

When I do not enable the "show items with no data", my visual is like this one:

See that from the 668 sales, only 458 have an employee assigned. I need to see the other 210 sales even though they do not have an employee assigned.

When I enable the "show items with no data", my visual is this one:

See that it still shows 458 sales from the 668. By enabling the "show blank data", it shows the available employees but it still does not show the remaining 210 sales that are not assigned to an employee. And when I export to excel, it only exports the 458 sales, whereas I want all the sales where the 210 sales that are not assigned can have a blank in the employee field.

Is this possible?

Best,

[v-zhenbw-msft]

Hi @Andvil ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Andvil you can choose show items with no value in your visual, for example for matrix visual, you can follow the screenshot below

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Andvil]

Hi @parry2k ,

I enabled the "show items with no data" option, it is the second picture of my post:

As you can see, the only difference from the first picture (that does not have the "show items with no data" option enabled) is that it shows all the available employees, but it does not show the 210 sales that have not been assigned to an employee.

So I already tried your solution, do you have any other suggestions?

Best,

[parry2k]

@Andvil where that 210 sales should get allocated? What is logic? 

[Andvil]

Hello @parry2k,

the 210 sales should be under the customer "01777794 Astet Agentes..". As seen on the picture, only 458 sales codes got assigned to an employee, but 210 sales codes were not assigned. 

This picture does not have the "show items with no data" feature, under the employee with 458 sales code I should have another row, where the employee field is blank and in the sales column I have the 210 value (number of sales codes not assigned to any employee).

The same thing with the next customer, under the rows with 392 and 7 sales codes assigned to employees, I should have another row with blank field in employees and the remaining sales codes that were not paired.

Here is a file that might help: PBI File 

[Greg_Deckler]

@Andvil - Maybe. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Andvil]

Hi @Greg_Deckler 

1) I attach an example file: PBI FILE 

2) My output is to have under each employee and the sales codes, another row with a blank (as no employees are assigned) and the remaining sales codes that were not paired. There are more explanations in my previous messages.

As you can see in this picture,  "01777794-Astet Agentes" has 668 purchases. 458 sales codes were assigned to an employee, however, 210 sales codes were not assigned and not shown in the table. Under the employee and the 458 sales, I should have a blank field in employee and then 210 under the 458, that way it matches the 668 total sales. I have enabled the "show items with no data" but still the information not assigned to an employee is not showing.

3) Power Bi must somehow show the information that has not been paired to an employee or agency, Imagine you have that column and not all the fields are filled with employee information, when adding the visual, you would have in blank all the sales that have not been assigned to an employee.

Best,

[v-zhenbw-msft · Accepted solution]

Hi @Andvil ,

Maybe you can use Measure instead of columns, like the following example.

Replace Item Column with Item Measure and put it in Values

Item Measure = 
var _x = CALCULATE(SUM('2020'[ITEMS]))
return
IF(
    ISBLANK(_x),"",_x)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.