Problem with data for Scatter chart

[FrankWe]

Hello all,

I trying to create a scatter chart in Power BI now and have big problems to get the necessary data for the chart that I would like to create. I tried a lot of different ways that I found by searching the internet but nothing is working so far. 

Below you can find the scatter chart how it should look like. under the following link to Power BI sample file you can find the Power BI file containing the data. 

The problem is that the dashboard is dynamic (the data can be filtered by different slicers) and I need to allocate the items to four different tiers (like an ABC Analysis). How the four tiers are seperated is defined in "DIM_Tiers". That means I need to calculate the the percentage of the GM value for each item from the total sum of GM for all items shown in the visual. On the tab "Running Sum" you can see a table visual already containing some of the needed data. this is working as long as you will not reset the slicers to show all data. Then I get the error that there are not enough ressources available.  I think I have the problem due to the fact that the ranking and also the vaule shown in column "FWE GM % of Selected" needs to be recalculated every time when the slicers will change. And when all slicers have been reseted ther will be to much data for which all these values needs to be recalculated. When this problem is solved the next problem will be to apply every item to one of the four tiers. For that I need to sort the itmes by the value of "FWE GM % of Selected". All items that have a sum larger than the value in "DMIN_Tiers/Value_From" (0) and smaller than the value in "DIM_Tiers\Value_to" (17,5) will be allocated to "Tier 1". This is a kind of ABC analysis based on the values in "FWE GM % of Selected". 

I think I will not be the first to have such a problem and somebody will be able to point me in the right direction how to do that. 

I hope that the description of the problem and what I would like to have is clear enough to understand my target. Any help will be appreciated. 

Frank

[v-venuppu]

Hi @FrankWe ,

I hope this information is helpful.If this answers your question, please accept it as a solution,so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @FrankWe ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you

[v-venuppu]

Hi @FrankWe ,

I wanted to check whether if you had a chance to review the information provided and solve the issue.

If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-venuppu · Accepted solution]

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.

[v-venuppu]

Hi @FrankWe ,

Sorry for the delay in response.

I am looking into the issue, will share the solution as soon as possible once I found some workarounds to solve the issue.

Thank you.

[v-venuppu]

Hi @FrankWe ,

Thank you for the Response..!!

Not sure if I understood correctly that I tried removing filter from text slicer "Item", but I was able to see the data.

Please find the screenshot for your reference:

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.

[FrankWe]

Hi, at the moment the problem is not in the scatter chart but in the table visual on tab "Table View". The table visual shoudl be sorted by "FWE GM % of Selected" and then I need to have a column with the cumulative sum of "FWE GM % of Selected" in each row. That is the first problem I have.  And in the next step then I need to allocate every item in the list to a "Tier". The settings of the "Tiers" are defined in "DIM_Tiers". All items where the cululative sum of "FWE GM % of Selected" is equal or less than 17,5 will be "Tier 1". The next items where the cululative sum of "FWE GM % of Selected" is larger than 17,5 and smaller than 35  will be "Tier 2". The same procedure for "Tier 3" and "Tier 4". You can have a look in the updated Power BI Example file. 

[v-venuppu]

Hi @FrankWe ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @burakkaragoz for the prompt response.

Ranking and tiering are done in memory using measures, not requiring a date field.

You don’t need Power Query or calculated columns – this is fully DAX-driven and responsive to slicers.

I have done some changes in the attached pbix file to replicate the scenario by creating two measures (FWE GM % Rank,Cumulative GM % and a column named Assigned Tier) as per my understanding, I hope it helps.

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.

[FrankWe]

Hi v-venuppu,

i just downloaded your pibx file and tried it. The problem I have still exists. Please try and remove as a test the filter text in the text slicer "Item" and see what happens. In my case I get the error that there are not enough resources available. That is the problem I have

[FrankWe]

I will have a look later on that. But there is no date field to sort the data. The data needs to be sorted on "FWE GM % of Selected" only using the filtered data/rows in the visual that has been filtered by the different slicers. So to be able to sort the list the values in "FWE GM % of Selected" need to be calculated first.

[burakkaragoz]

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:

1. 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'))
   )

1. 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.

2. 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.

3. 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.

4. 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

[FrankWe]

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?

[burakkaragoz]

@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:

1. Sort your data
   Sort by IDCustomer, IDProduct, and Validity From (ascending).

2. Add an Index column
   This helps track the order of price changes.

3. Group the data
   Group by IDCustomer and IDProduct.

4. 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
    FillDown

This 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.