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

     

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