Forum Discussion

adriankohws's avatar
adriankohws
Regular Visitor
1 year ago
Solved

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%

 

 

Thank you very much.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

4 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    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's avatar
      adriankohws
      Regular Visitor
      1 year ago

      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's avatar
    adriankohws
    Regular Visitor
    1 year ago

    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?
     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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