Order Level Margin % Data

[Dave1mo1]

Hi,

My data is imported at the order line level. I have the measure below to calculate margin at the order line, and can be aggregated by listing just the order in a table or matrix. However, I've been asked to provide both the margin % at the order line and at the entire-order level in the same visual, which is why I need a new measure that shows the aggreggated margin % for the entire order. 

Can you help?

Booked - Margin Percent = [Booked - Total Margin]/[Booked - Total Sales]

[Dave1mo1]

Hi, Carson.

Thanks so much for your response. Unfortunately, I'm having an issue, in both Desktop & Service, of the visual not being able to be displayed due to a lack of memory. When I filter down to just a few orders, I'm getting the same value for all orders. Below is the DAX for the measure. I can try to upload a test PBIX file at some point today, but I'm not sure the most efficient way to strip out all of the confidential information from my PBIX file.

One thing to note - Each row in my data has both an order number AND an order line. Not sure if that makes a difference. 

XBooked - Total Order Margin Percent =
VAR TotalMargin = CALCULATE(

SUMX('Sales Fact Table',[Booked - Total Margin]),

FILTER(ALL('Sales Fact Table'),'Sales Fact Table'[Ord Num]=MAX('Sales Fact Table'[Ord Num])))
VAR TotalSales = CALCULATE(

SUMX('Sales Fact Table',[Booked - Total Sales]),

FILTER(ALL('Sales Fact Table'),'Sales Fact Table'[Ord Num]=MAX('Sales Fact Table'[Ord Num])))
RETURN
DIVIDE(TotalMargin, TotalSales, 0)

[Anonymous]

Hi,@Dave1mo1 I am glad to help you.
Based on your description, it appears that you are experiencing an out of memory problem with the MEASUREMENT that I was using originally provided by me
(Calculate the profit percentage for the entire order)
After trying it I recommend you to use my following code

AllExcept_Total Margin Percent = 
VAR TotalMargin = 
    CALCULATE(
        SUMX('Table','Table'[Booked - Total Margin]),
        ALLEXCEPT('Table', 'Table'[OrderID])
    )
VAR TotalSales = 
    CALCULATE(
        SUMX('Table','Table'[Booked - Total Sales]),
        ALLEXCEPT('Table', 'Table'[OrderID])
    )
RETURN
DIVIDE(TotalMargin, TotalSales, 0)
//The best way

The calculation logic for AllExcept_Total Margin Percent is simpler, directly in the existing context, rather than the same as measure[Total Order Margin Percent]:
Filter the data with the FILTER and MAX functions. This reduces computational complexity and memory usage.
The original measure.

Total Order Margin Percent = 
VAR TotalMargin = CALCULATE(SUMX('Table',[Booked - Total Margin]),FILTER(ALL('Table'),'Table'[OrderID]=MAX('Table'[OrderID])))
VAR TotalSales = CALCULATE(SUMX('Table',[Booked - Total Sales]),FILTER(ALL('Table'),'Table'[OrderID]=MAX('Table'[OrderID])))
RETURN
DIVIDE(TotalMargin, TotalSales, 0)

The fact that you are experiencing visual out-of-memory problems indicates that your data is too large. In fact, it is recommended that you optimize your data model to ensure that there are no unnecessary columns and tables in the data model to reduce the memory footprint.
Pre-aggregate data as much as possible and use aggregated tables to perform operations.
Reduce unnecessary data, you can do this by filtering or aggregating to ensure that only the necessary data is processed, reducing the number of memory problems.
This is also a headache, optimizing the dataset itself is the best approach
I've uploaded the test file, hope it helps!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,@Dave1mo1 I am glad to help you.

According to your description, you have successfully created a measure to calculate the profit percentage for the order level, you now need a measure to calculate the aggregated profit percentage for the whole order level on top of the original one.

As you have not given a specific data example, I have created my own simple test data that you can refer to. If my simulation of your model data fails and does not match your actual data model, please point it out and share your test pbix file that does not contain sensitive data and matches your current data format.
how to share your file.
URL:
https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FCommu...

Here is my test data, I hope it will be helpful to you
Assuming that there are multiple orders (different order IDs), each with a different Product, and each Product has a corresponding Sales and Margin

Here is my dax code:

Booked - Margin Percent = [Booked - Total Margin]/[Booked - Total Sales]

Total Order Margin Percent = 
VAR TotalMargin = CALCULATE(SUMX('Table',[Booked - Total Margin]),FILTER(ALL('Table'),'Table'[OrderID]=MAX('Table'[OrderID])))
VAR TotalSales = CALCULATE(SUMX('Table',[Booked - Total Sales]),FILTER(ALL('Table'),'Table'[OrderID]=MAX('Table'[OrderID])))
RETURN
DIVIDE(TotalMargin, TotalSales, 0)

Booked - Margin Percent This measure calculates the percentage of each Product in each order ProductA: 20/100=20%.
Total Order Margin Percent This measure calculates the percentage of the entire order (20+50)/(100+200)=23.33%.

As a conclusion, Total Order Margin Percent calculates the percentage of the entire order based on the original measure: [Booked - Margin Percent
Group Aggregation
Note that the code I've provided will most likely not fit your real calculation environment, so you need to pay more attention to how to implement the aggregation of orders into groups to find the percentage of each order as a whole.
You need to write a measure based on your real data, I hope my suggestion will help you.
If you can, please share your test pbix file without sensitive data to show the structure of your real data model, it will help you to find the right suggestion faster.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.