created new customers with sales based on formula not appearing in matrix table

[Lisa_2021]

In original table, I have 3 customers (A, B, C).  Based on these 3 customers, I created a measure called "new_sales" which includes the data for the original A,B,C and calculated 2 additonal sales for customers (D, E) by adding sales of A+B = D customer and B-C=E customer.  I created new measure using VAR and SWITCH.  Something like:

new sales = 
VAR _A = calculate (sum (sales), customer = "A")
VAR _B = calculate (sum (sales), customer = "B")
VAR _C = calculate (sum (sales), customer = "C")
VAR D = _A + _B
VAR E = _B - _C

VAR final_sale = switch( [customer],
"A",  _A,
"B",  _B,
"C", _C,
"D", _D,
"E", _E)
RETURN
final_sale


In matrix table using a dimension table for customer which has customer A,B,C,D,E, "new_sales" measure only show sales for customer A,B,C.  The new customers D,E wouldn't appear.  This problem is driving me nuts.  This is my first time posting.  I am new to power bi.  Please help.     

[serpiva64]

Hi,

try something like this calculated column in Customer:

TotSales = Switch( TRUE(),

'02 Customer'[Customer]="D", sumx(FILTER('01 Sales','01 Sales'[Customer]="A"),'01 Sales'[#Sales]) +sumx(FILTER('01 Sales','01 Sales'[Customer]="B"),'01 Sales'[#Sales]),

'02 Customer'[Customer]="E", sumx(FILTER('01 Sales','01 Sales'[Customer]="B"),'01 Sales'[#Sales]) -sumx(FILTER('01 Sales','01 Sales'[Customer]="C"),'01 Sales'[#Sales]),

'01 Sales'[#Sales])

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[Lisa_2021]

Unfortunately it didn't work.

Just like before, it works if it's a standalone table.  However, when there's a slicer switching from customer to customer, the customer D & E do not show up.

[serpiva64]

Hi, in my exemple they show up because i created customer D & E in the customer table.

You need to add customer D & E in the customer table and then, if there is a relationship between customer and sales, it'll function.

[Lisa_2021]

Thanks but I do have a relationship between them.  The dim_customer table has customer D & E but the original table doesn't.   Please see the diagram.

[serpiva64]

Sorry, but in your diagram there is only one table, one measure and one "matrix table" that i suppose i a matrix visual. Only by calculating a measure or adding a calculated column you don't add the needed customer.

If in my exemple i start without client D & E i need to create the 2 new customer by something like this (it's a new table)

03 DimCustomer = union('02 Customer',row("Customer","D"),row("Customer","E"))

and then add the calculated column referencing the column:

'03 DimCustomer'[Customer]

 Here is the new calculated column

TotSales = Switch( TRUE(),

'03 DimCustomer'[Customer]="D", sumx(FILTER('01 Sales','01 Sales'[Customer]="A"),'01 Sales'[#Sales]) +sumx(FILTER('01 Sales','01 Sales'[Customer]="B"),'01 Sales'[#Sales]),

'03 DimCustomer'[Customer]="E", sumx(FILTER('01 Sales','01 Sales'[Customer]="B"),'01 Sales'[#Sales]) -sumx(FILTER('01 Sales','01 Sales'[Customer]="C"),'01 Sales'[#Sales]),

'01 Sales'[#Sales])

[Lisa_2021]

Hi.  Is there a way I can upload pbix?  

The syntax is incorrect.  

TotSales = SWITCH(TRUE(),

Sales[Customer]="A", SUMX(FILTER(Sales,Sales[Customer]="A"),Sales[Sales]),

Sales[Customer]="B", SUMX(FILTER(Sales,Sales[Customer]="B"),Sales[Sales]),

Sales[Customer]="C", SUMX(FILTER(Sales,Sales[Customer]="C"),Sales[Sales]),

Sales[Customer]="D", SUMX(FILTER(Sales,Sales[Customer]="A"),Sales[Sales])) + SUMX(FILTER(Sales,Sales[Customer]="B"),Sales[Sales]),

Sales[Customer]="E", SUMX(FILTER(Sales,Sales[Customer]="B"),Sales[Sales])) - SUMX(FILTER(Sales,Sales[Customer]="C"),Sales[Sales])

[serpiva64]

try to upload file

https://community.powerbi.com/t5/Desktop/upload-pbix/m-p/1171649#M528305

[Lisa_2021]

Unfortunately my company has blocked the upload site. 

I think with an email address I can send you a link to this pbi on our sharepoint but I'm not certain it's going to work.

[Lisa_2021]

[serpiva64 · Accepted solution]

Sorry i forgot to answer to a previous question:

#Sales = sum('Sales'[Sales])

TotalSales is a column in dim_customer list, not in sales 

TotSales = Switch( TRUE(),

'dim_ customer list'[Customer]="D", sumx(FILTER('Sales','Sales'[Customer]="A"),'Sales'[#Sales]) +sumx(FILTER('Sales','Sales'[Customer]="B"),'Sales'[#Sales]),

'dim_ customer list'[Customer]="E", sumx(FILTER('Sales','Sales'[Customer]="B"),'Sales'[#Sales]) -sumx(FILTER('Sales','Sales'[Customer]="C"),'Sales'[#Sales]),

'Sales'[#Sales])

[Lisa_2021]

OMG.  it worked!!!!  You are awesome.  Thank you so much!

[Lisa_2021]

Thank you Serpiva64.   Unfortunately I am away for a week.   I will test it out next weekend and let you know.   Thanks again.  Lisa

[serpiva64]

Hi,

can you post the measure "new_sales"?

[Lisa_2021]

Hi. serpiva64.  We are on the same page.  Just posted my measure for "new_sales"  Thanks!