Forum Discussion

Dave1mo1's avatar
Dave1mo1
Icon for Helper III rankHelper III
2 years ago

Order Level Margin % Data

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'v...
Dave1mo1's avatar
Dave1mo1
Icon for Helper III rankHelper III
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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.