Forum Discussion
Calculating invoice margin total
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
you need a measure like
calculate([Margin], allexcept(Table, Table[Invoice Number]))
calculate([Margin], filter(allselected(Table), Table[Invoice Number]) = max(Table[Invoice Number]) ) )
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
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
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