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 e...
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

     

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.