Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
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.