Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
adriankohws
Regular Visitor

Customer Sales Grouping Report

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%

adriankohws_0-1727771666980.png

 

 

Thank you very much.

4 REPLIES 4
adriankohws
Regular Visitor

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?
 
adriankohws_0-1727835775235.png

 

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

vzhouwenmsft_1-1727843972001.png

vzhouwenmsft_0-1727843832620.png

Please follow these steps:

1.Enter the following data manually

vzhouwenmsft_2-1727844191609.png

2.Sort by column

vzhouwenmsft_4-1727844351976.png

 

3.Creating table-to-table relationship

vzhouwenmsft_3-1727844312737.png

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

vzhouwenmsft_5-1727845029828.png

 

Best Regards,
Wenbin Zhou

 

DataNinja777
Super User
Super User

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,

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.

 

adriankohws_0-1727834733403.png

 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors