Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to 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
I tried this and it worked to create the measure.
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |