Forum Discussion
Problem with data for Scatter chart
Hi FrankWe ,
Thank you for your Patience..!!
I have created new table under "Table View" tab , please have a look at the attached pbix file.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you.
Hi FrankWe ,
Thanks for the detailed explanation — it really helps to understand the challenge.
What you're trying to achieve is definitely doable in Power BI, but the performance issue you're hitting is likely due to the way the ranking and percentage calculations are being evaluated across a large dataset when slicers are reset.
Here’s a structured approach you can try:
- Optimize the "FWE GM % of Selected" measure
Make sure you're using ALLSELECTED() instead of ALL() or REMOVEFILTERS() when calculating the total GM. This keeps the context limited to what's visible after slicers are applied.
FWE GM % of Selected =
DIVIDE(
[FWE GM],
CALCULATE([FWE GM], ALLSELECTED('YourTable'))
)Pre-calculate tiers if possible
If your tier logic is static (based on fixed thresholds), consider creating a calculated column or a separate table that maps each item to a tier based on its GM % value. This avoids recalculating tiers on the fly.Use RANKX efficiently
Ranking can be expensive. Try to avoid using it in visuals directly. Instead, pre-calculate ranks in a calculated column if the dataset isn’t too large, or use it in a summarized table visual with limited rows.Consider aggregating data
If the dataset is too large, try aggregating it at a higher level (e.g., product category instead of individual items) before applying the ABC logic.Performance tip
If you’re getting the “not enough resources” error, try disabling interactions between slicers and visuals temporarily to isolate the bottleneck.
Let me know if you want help writing the exact DAX expressions or optimizing the model structure.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI
First of all , thank you for your reply. I already used the following measures in my model:
===============================
FWE GM Total (Visible Rows) =VAR DataSubSet = ALLSELECTED(FCT_SampleData)VAR GMTotal =CALCULATE(SUM(FCT_SampleData[Gross Margin (S)]),DataSubSet)RETURN GMTotal
====================FWE GM % of Selected = DIVIDE(SUM(FCT_SampleData[Gross Margin (S)]),[FWE GM Total (Visible Rows)],0)
I think this should be okay so far, isn't it?FrankWe ,
Thanks for the follow-up. Based on your message and the DAX snippets, it looks like you're trying to dynamically calculate values based on filtered context, and possibly apply tier logic or running calculations.
Since you're open to using Power Query, here's a basic example of how you can simulate the "Current Price" logic step-by-step using M code:
Power Query Steps:
Sort your data
Sort by IDCustomer, IDProduct, and Validity From (ascending).Add an Index column
This helps track the order of price changes.Group the data
Group by IDCustomer and IDProduct.Apply a custom function
Inside each group, use a loop to calculate the "Current Price" row by row:
(Table as table) => let Sorted = Table.Sort(Table, {"Validity From", Order.Ascending}), AddIndex = Table.AddIndexColumn(Sorted, "Index", 0, 1, Int64.Type), AddPrice = Table.AddColumn(AddIndex, "Current Price", each if [Index] = 0 then [OriginalPrice] else null ), FillDown = List.Accumulate( {1..Table.RowCount(AddPrice)-1}, AddPrice, (state, i) => let prev = state{i-1}[Current Price], coeff = state{i}[Coefficient], updated = Table.ReplaceValue(state, null, prev * coeff, Replacer.ReplaceValue, {"Current Price"}) in updated ) in FillDownThis logic assumes the first row starts with the original price and coefficient 1, and each following row multiplies the previous price by the current coefficient.
Let me know if you want me to help you plug this into your actual table or adjust it for your column names.
