Customer Sales Grouping Report

[adriankohws]

I have a table of sales transactions with customer name. I need a grouping report showing months across columns, this is simple nothing much. However, the report requires a % of the total sales for each of the customer over the total sales value for the selected period within the filter.

I actually tried to create a measure of sales amount group by customer, then I created another measure using this value to divide with the total sales value, but it didn't allow me to create the measure stating that there is a circular referencing issue. Can someone advise me how to achieve what I wanted? Any help is much appreciated, I am quite new to Power BI and not frequency developing reports.

So let's say the total sales is ten thousand, what I need for another column is showing 1458/10000 x 100%

Thank you very much.

[adriankohws]

I tried this and it worked to create the measure.

SalesCoverage =

DIVIDE ( Sum(PostedSalesInvoiceHeader[Amount]), CALCULATE ( Sum(PostedSalesInvoiceHeader[Amount]), ALL ( PostedSalesInvoiceHeader[Sell_to_Customer_Name] ) ) )

However, how to add as a column in the matrix?

[v-zhouwen-msft · Accepted solution]

Hi DataNinja777 ,thanks for the quick reply, I'll add more.

Hi @adriankohws ,

Assuming this was my situation at the beginning, it should be similar to your scenario. If there is a big difference, please share the .pbix file without sensitive data.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Please follow these steps:

1.Enter the following data manually

2.Sort by column

3.Creating table-to-table relationship

4.Use the following DAX expression to create a measure

Measure = 
VAR _denominator = CALCULATE(SUM('Table'[Amount]),ALL('Table'))
VAR _numerator = CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Name]))
VAR _column = SELECTEDVALUE('Table 2'[Month])
RETURN IF( _column = "%",FORMAT(DIVIDE(_numerator,_denominator),"0.00%"),SUM('Table'[Amount]))

5.Final output

19.23% = 150 / 780

Best Regards,
Wenbin Zhou

[DataNinja777]

Hi @adriankohws ,

The measure below calculates the percentage of total sales that each customer contributes within the selected period. 

% of Overall Sales =
DIVIDE ( [Sales], CALCULATE ( [Sales], ALL ( Customer ) ) )

 Best regards,

[adriankohws]

Hi DataNinja777, thank you very much for your input, I tried but I hit into some issue, which I am not sure why, maybe I have to first create a total measure for the sales_amount value? The 'Amount' field is currently a numeric field keeping the sales figure for each transaction.