Calculating invoice margin total

[laetitiaf]

Hey, 

I'm stuck on how I can summarise the invoice margin for the whole invoice and was wondering how this can be achieved in a dax measure?

For example, in this table, we have each SKUs margin (in red), but next to it I want to be able to see the total margin for the same invoice number

So the above "margin invoice total" should correlate with the below.

[v-yiruan-msft]

Hi @laetitiaf ,

Whether your problem has been resolved? If yes, could you please mark the helpful reply as Answered? It would be helpful for others in the community if they face the similar problem as yours. Thank you.

Best Regards

[amitchandak]

@laetitiaf ,

you need a measure like

calculate([Margin], allexcept(Table, Table[Invoice Number]))

calculate([Margin], filter(allselected(Table), Table[Invoice Number]) = max(Table[Invoice Number]) ) ) 

[laetitiaf]

First syntax I get an error stating that the query has exceeded the available resources - I also have the data filtered down to todays date

[v-yiruan-msft · Accepted solution]

Hi @laetitiaf ,

Please create a measure as below to get it and check if it can return the correct result...

Margin Invoice Total =
VAR _invnum =
    SELECTEDVALUE ( 'Table'[Invoice Number] )
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Invoice Number] = _invnum ),
        [Margin]
    )

If the above one can't help you figure out, please provide some raw data in your tables(exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[laetitiaf]

Hi Rena, 

Apologies I have only been able to look back at this now.
Your solution works, but still results in breaking the visual, resulting in a query exceededs the available resources error when I add in anything from the "SKU" table.

Example of the data output that is sufficient 

SKU	Quantity	Cost (per unit)	Sell Price (per unit)	Margin	Margin invoice total	Invoice Number	Customer ID
5603	5	4.14	5.49	24.59%	18.28%	D27835	56793
5916	2	10.23	11.5	11.04%	18.28%	D27835	56793
38483	1	16.68	21.85	23.66%	18.28%	D27835	56793
44347	2	148.66	176.17	15.62%	18.28%	D27835	56793
44347	2	315.9	374.36	15.62%	18.28%	D27835	56793
45136	1	129.73	150.51	13.81%	18.28%	D27835	56793
47712	2	11.43	14.97	23.65%	18.28%	D27835	56793
48971	2	112.73	121.75	7.41%	7.41%	D27838	68223
5010	1	19.11	22.37	14.57%	21.41%	D27841	54426
50344	1	0.01	0		21.41%	D27841	54426
6819	3	3.7	4.85	23.71%	21.41%	D27841	54426
6829	2	3.53	4.68	24.57%	21.41%	D27841	54426
10888	1	23.51	30.8	23.67%	21.41%	D27841	54426
38724	13	3.5	4.19	16.47%	21.41%	D27841	54426
38728	3	15.48	20.28	23.67%	21.41%	D27841	54426
38729	1	4.38	5.48	20.07%	21.41%	D27841	54426
42133	20	5.19	6.8	23.68%	21.41%	D27841	54426
42492	1	27.05	33.81	19.99%	21.41%	D27841	54426
48880	4	36.24	47.49	23.69%	21.41%	D27841	54426

Then once "Product Name" is added, query exceededs the available resources 

SKU	Product Name	Quantity	Cost (per unit)	Sell Price (per unit)	Margin	Margin invoice total	Invoice Number	Customer ID
5603	Premium Black Nylon Cable Ties, 300mm (Pack of 10)	5	4.14	5.49	24.59%	18.28%	D27835	56793
5916	Heavy-Duty Stainless Steel Hose Clamps	2	10.23	11.5	11.04%	18.28%	D27835	56793
38483	Weatherproof Junction Box, IP68 Rated	1	16.68	21.85	23.66%	18.28%	D27835	56793
44347	Industrial-Grade Cable Glands (Pack of 5)	2	148.66	176.17	15.62%	18.28%	D27835	56793
44347	Industrial-Grade Cable Glands (Pack of 5)	2	315.9	374.36	15.62%	18.28%	D27835	56793
45136	Multi-Purpose Electrical Insulation Tape, 10m Roll	1	129.73	150.51	13.81%	18.28%	D27835	56793
47712	UV Resistant Cable Clips, 25mm (Pack of 50)	2	11.43	14.97	23.65%	18.28%	D27835	56793
48971	High-Performance Surge Protector Power Board	2	112.73	121.75	7.41%	7.41%	D27838	68223
5010	Compact LED Floodlight, 30W	1	19.11	22.37	14.57%	21.41%	D27841	54426
50344	Durable PVC Conduit, 20mm (2m Length)	1	0.01	0		21.41%	D27841	54426
6819	Modular Circuit Breaker, 16A	3	3.7	4.85	23.71%	21.41%	D27841	54426
6829	Advanced Digital Timer Switch, 24-Hour Programmable	2	3.53	4.68	24.57%	21.41%	D27841	54426
10888	Flexible Heat Shrink Tubing Assortment	1	23.51	30.8	23.67%	21.41%	D27841	54426
38724	Waterproof LED Strip Light Kit, 5m	13	3.5	4.19	16.47%	21.41%	D27841	54426
38728	Smart Wi-Fi Plug with Energy Monitoring	3	15.48	20.28	23.67%	21.41%	D27841	54426
38729	High-Torque Electric Screwdriver Kit	1	4.38	5.48	20.07%	21.41%	D27841	54426
42133	Compact Power Distribution Board, 4-Way	20	5.19	6.8	23.68%	21.41%	D27841	54426
42492	Adjustable Cable Tray System, 600mm	1	27.05	33.81	19.99%	21.41%	D27841	54426
48880	Low-Voltage Garden Lighting Transformer	4	36.24	47.49	23.69%	21.41%	D27841	54426