How to get Percentage at a Column by dividing from a specific Account

[vinayguptak0520]

Hi everyone.  I have been struggling with a DAX calculation where I would like to get Percentage by Column.

				Actual Total Sales	Percentage		Formula for %
40000	Sales			$500,000.00
Sales Allowance	Allowances			$88,350.00	17.67%		E4/E$2
56000	Advertising Expense			$11,000.00	2.20%		E5/E$2
56100	Allowance			$75,000.00	15.00%		E5/E$2
56200	Allowance-DefectiveGoods			$500.00	0.10%		E6/E$2
56300	Discount			$-	0.00%
56400	Special Program			$650.00	0.13%
56500	Cust Warehouse			$-	0.00%
56600	Freight Discount			$1,200.00	0.24%
Sales Deductions	Deductions			$3,890.00	0.78%		E12/E$2
38000	Deduction - Freight			$-	0.00%
38010	Customer Charge Back			$-	0.00%
38020	Other Customer Chargebacks			$2,330.00	0.47%
38030	Drop Ship Errors			$1,500.00	0.30%
38040	Deduction - Defective Goods			$60.00	0.01%
38050	Shortage			$-	0.00%
Grand Total				$407,760.00	81.55%		E20/E$2

I am trying to get the percentage calculated.  Take a specific Company's total sales (ie Amazon) = 500,000 and divide that by its sales to get the percentage.

Example: 88,350/500,000 = 17.67%

11,000/500,000 = 2.20%

I've used this DAX but this only gives me the percentage of the Grand Total.

ActualPerct =
VAR _TotalSales40000Actual =
CALCULATE(
SUM('CustomerData'[Actual]),
'CustomerData'[Account] = 40000
)

VAR _TotalSales = SUM('CustomerData'[Actual])

VAR _Result =
DIVIDE(
SUM('CustomerData'[Actual]),
_TotalSales40000Actual,0
)

RETURN _Result

Any suggestions or guidance is highly appreciated.  Thank you

[vinayguptak0520]

Thank you so much for the suggestion.  Do I have to create the summary level in Power Query or can that be created in the report view using DAX to summarize the table?  The reason is that the raw data is at a daily level and will continue to grow.  Thank you.

[lbendlin]

It is impossible to do this in DAX, must be done even before Power Query.

[vinayguptak0520]

I see.  This is what the raw data Excel file looks like.  I have to join multiple tables to get the Category and subcategories.

Account	FinDate	CustomerVendorID	CustomerVendor	Parent	NetAmt	Actual	Budget	Customer Group
40000	1/6/2024	C00088	Walmart - CONSIGNMENT	C00411	-106.43	-106.43	0.00	Walmart
40000	1/6/2024	C00088	Walmart - CONSIGNMENT	C00411	-2131.16	-2,131.16	0.00	Walmart
40000	1/6/2024	C00088	Walmart - CONSIGNMENT	C00411	-3112.85	-3,112.85	0.00	Walmart
40000	1/6/2024	C00088	Walmart - CONSIGNMENT	C00411	-1540	-1,540.00	0.00	Walmart
40000	1/6/2024	C00088	Walmart - CONSIGNMENT	C00411	15.24	15.24	0.00	Walmart
40000	1/6/2024	C00088	Walmart - CONSIGNMENT	C00411	1674	1,674.00	0.00	Walmart

Thank you.

[lbendlin]

you may not want to post your sensitive company data here. Post sample data only.

[vinayguptak0520]

Those are sample data.  Thank you.

[lbendlin]

if that is the case can you provide them in a usable format, not as a screenshot?

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

[vinayguptak0520]

Thank you for the suggestion.  I went ahead and reposted the raw data.  The only difference is that I have added the Financial date and parent name.  I took your logic and tried to incorporate that in the PBI example but the percentages are incorrect.  Also, there are multiple Groups.  This is just an example of 1 group.

Class	Account	FinDate	CustomerVendorID	CustomerVendor	Parent	Actual	Group
COGS	35000	1/6/2024	C00088	Walmart - CONSIGNMENT	C00411	$   1,020.00	Walmart
COGS	35100	2/10/2024	C00088	Walmart - CONSIGNMENT	C00411	$       650.00	Walmart
Sales	40000	1/6/2024	C00088	Walmart - CONSIGNMENT	C00411	$   2,451.00	Walmart
Sales	40000	1/6/2024	C00088	Walmart - CONSIGNMENT	C00411	$   3,450.00	Walmart
Sales	40000	1/6/2024	C00088	Walmart - CONSIGNMENT	C00411	$   1,500.00	Walmart
Sales Allowance	53450	1/6/2024	C00088	Walmart - CONSIGNMENT	C00411	$       754.00	Walmart
Sales Allowance	53450	1/20/2024	C00088	Walmart - CONSIGNMENT	C00411	$   1,245.00	Walmart
Sales Allowance	53450	1/15/2024	C00088	Walmart - CONSIGNMENT	C00411	$     (115.00)	Walmart
Sales Allowance	53460	2/20/2024	C00088	Walmart - CONSIGNMENT	C00411	$   1,240.20	Walmart
Sales Discount	25400	1/6/2024	C00088	Walmart - CONSIGNMENT	C00411	$   1,540.00	Walmart

Desired output.

Is it due to the data being more granular?  Any suggestion?  Thank you.

[lbendlin]

Your Sales Discount number is off.

I had to introduce a sort column for the Class column.

[vinayguptak0520]

Are you still performing group by in the power query?  Thanks.

[lbendlin]

no grouping. see attached.

[vinayguptak0520]

Thank you so much for the suggestion.  I used your formula with my actual data and noticed that the Sales numbers are incorrect.  For example, it should say $400,000 but it's displaying $350,000.  Is it because I have a lot more data fields in the raw data set?

00_TEST_Actual =

VAR _TotalActuals = SUMMARIZE(SummaryData_TEST,[Account],[Actual])

RETURN

    SUMX(_TotalActuals,IF([Account] = 40000,[Actual],-[Actual]))

I appreciate all the help.

[vinayguptak0520 · Accepted solution]

Thank you so much for your suggestion.  I did some minor tweaks and was able to get the results that I was looking for.

[lbendlin]

Your issue is not with the DAX but with the general structure of your data. It is in an Excel based user friendly format, but unusable for Power BI.  You need to clean that up first and then the DAX writes itself.

The Total Sales for example should ideally not be part of this table at all, it should come from a separate table.

Here's an example of how this would look like in Power BI: