Measure to SUM all rows (in table visual) with same ID

[akhoury]

Hello DAX Gurus,

I am having trouble with a relatively simple matter:

I have 1 table with sales transactions, per customer, per product, per day.

Created a measure  [Sales Vol..] which Sums the column for sales value.

I am creating the below table as a visual, and need to have for each Customer-Product row, the sales for the product in the row, and in another column the total sales of that customer (depicting the sum of the red rectangles.

The below measure is not working, it shows the sales of the current product, not the sum of all product for that particual customer.

Thanks in advance, your time is greatly appreciated.

Aminek

[amitchandak]

@akhoury , seems like you need sun total

then try like the example

Calculate(Sum(Table[Sales Vol]), allexcept(Table, Table[ID]))
Calculate(Sum(Table[Sales Vol]), filter(allselected(Table), Table[ID] = Max(Table[ID])))

Change the column as per need

Refer example

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

[akhoury]

Thanks for your reply @amitchandak .

Unfortunatly, those did not work. I had already tried them and retried them now.

Using Allexcept hangs and runs out of memory.

And filtering by ID=MAX(ID) returns sales of current product. Not summing all products for same customers.  

[amitchandak]

@akhoury ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[akhoury]

Thanks for replying @amitchandak . 

Below is the table in my model (called SALES) on which Im doing those calculations:

Date	Customer ID	Product	Sales vol
05/04/2020	4729	SKU 82	34.6
06/01/2020	4729	SKU 89	80.6
03/05/2020	4729	SKU 89	92.2
06/01/2020	4729	SKU 9	63.4
16/03/2020	6942	SKU 122	451.2
13/04/2020	6942	SKU 116	374.4
04/05/2020	6942	SKU 116	374.4

I have a measure that sums the Sales column: Sales Volume = SUM(sales[Sales vol])

My end objective is, to calculate the % share for every Product bought by every customer. And for this, all Im missing is the total sales per customer.

Resulting Table visual as such:

Customer ID	Product	Sales	Total sales per customer	% share
4729	SKU 82	34.6	270.8	13%
4729	SKU 89	172.8	270.8	64%
4729	SKU 9	63.4	270.8	23%
6942	SKU 122	451.2	1200.0	38%
6942	SKU 116	748.8	1200.0	62%

% share is just the division of Sales by 'Total Sales per Customer'; what Im missing is Total sales per customer.

Ideally I would need a measure for this. I know how to do it using a Calculated Columns with the EARLIER function, but its quite inefficient from a performance point of view. The table has 4-5millions of rows.

Thanks in advance for your help.

Aminek

[Greg_Deckler]

@akhoury - Can you just use Show value as | Percent of Grand Total?

[akhoury]

Apologies for the delay @Greg_Deckler - swamped..

The Gran Total is the total volume of all customers. What I need is the total volume per customer as a measure so to later use it to calculate the Product Shares within each customer.

[harshnathani]

Hi @akhoury ,

USe these measures

Sales = SUM('Table'[Sales vol])

Total Sales per customer = CALCULATE(SUM('Table'[Sales vol]), ALLEXCEPT('Table','Table'[Customer ID]))

% share = DIVIDE([Sales],[Total Sales per customer])

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[akhoury]

Thanks for your reply @harshnathani .

Unfortunatly I am getting a different (and weird) result than yours.

As in the screenshot below - Product IDs as well as the Sales values disappear and the Total sales per customer seem to be way higher than it should. Possibly summing all the column:

[harshnathani]

Hi @akhoury ,

Can you share sample .pbix file.

Regards,

HN

[akhoury]

Thanks for replying @harshnathani . Unfortunatly it will take me too much time to anonimize the company's PBI file. It's quite large.

However, I found the cause of the proble, but I'm not able to solve it: It seems that the below DAX measure..

Total volume per customer = CALCULATE(SUM(oos_sales[sales - volume (L)]), ALLEXCEPT(oos_sales,oos_sales[customer_unique_code]))

..is forcing the table visual to list ALL customers, irrespective of any filter. I have a Channel filter applied, and it seems that the ALLEXCEPT is not letting the slicer to filter our the NON Retail customers as shown in the pic below, hence why the Sale Volume is blank but the Total Sales per customer is not:

Screenshot

Thanks for the help. Much appreciated.

Aminek

[Greg_Deckler · Accepted solution]

@akhoury - I believe I got this:

Measure 11 = 
    VAR __Customer = MAX('Table (11)'[Customer ID])
    VAR __Sum = SUM('Table (11)'[Sales vol])
    VAR __Total = SUMX(FILTER(ALL('Table (11)'),[Customer ID]=__Customer),[Sales vol])
RETURN
    DIVIDE(__Sum,__Total,0)

PBIX attached below sig, Page 11 

[akhoury]

Awesome - works perfect. Nice trick using the cust_ID=Max(Cust_ID) - I didnt know it works on Texts/strings same as with numbers.

Thanks @Greg_Deckler  for the solution and to all others that share their thoughts.