topic How to get Percentage at a Column by dividing from a specific Account in DAX Commands and Tips

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

vinayguptak0520 — Thu, 28 Mar 2024 13:01:00 GMT

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

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

lbendlin — Thu, 28 Mar 2024 19:09:15 GMT

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:

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

vinayguptak0520 — Thu, 28 Mar 2024 21:59:48 GMT

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.

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

lbendlin — Thu, 28 Mar 2024 22:19:43 GMT

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

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

vinayguptak0520 — Thu, 28 Mar 2024 22:49:42 GMT

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.

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

lbendlin — Thu, 28 Mar 2024 22:40:40 GMT

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

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

vinayguptak0520 — Thu, 28 Mar 2024 22:42:15 GMT

Those are sample data. Thank you.

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

lbendlin — Thu, 28 Mar 2024 22:44:50 GMT

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-Forum/ba-p/963216

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

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

vinayguptak0520 — Sat, 30 Mar 2024 00:33:22 GMT

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.

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

lbendlin — Sat, 30 Mar 2024 01:01:38 GMT

Your Sales Discount number is off.

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

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

vinayguptak0520 — Sat, 30 Mar 2024 01:51:54 GMT

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

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

lbendlin — Sat, 30 Mar 2024 01:58:30 GMT

no grouping. see attached.

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

vinayguptak0520 — Sat, 30 Mar 2024 02:10:39 GMT

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.

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

vinayguptak0520 — Sun, 31 Mar 2024 16:31:56 GMT

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