Wrong or incorrect total sums / problem with aggregation of data

[Anonymous]

Hi guys,

my company is buying some of our raw materials on the Spotmarket and some orders are getting bundled and bought on the Forecast market.

Needless to say, bundling and buying on the Forecast market is cheaper, so I'd like to show the saving potential if 100% is bought through Forecasts for different regions / different companies and so on for different raw materials (see column "Code" in my Screenshots).

Unfortunately as this is company data, I can't post the pbix file.

I have a data table - call it "Items" - that looks like this:

And what I want to show is something like this:

--> As I mentioned I also have a slicer with different raw material codes like H01, H02, S01, O01,...

The user should be able to see the savings for one or multiple codes selected at once.

My first approach was to build a measure like this:

Savings_potential = 
VAR Avg_FC_price = 
    CALCULATE(
        SUM(items[Total]) / SUM(items[Quantity]),
        Items[FC / Spot] == "FC")

VAR Avg_SP_price = 
    CALCULATE(
        SUM(items[Total]) / SUM(items[Quantity]),
        Items[FC / Spot] == "Spot")

VAR Quantity_Spot = 
    CALCULATE(
        SUM(Items[Quantity]),
        Items[FC / Spot] == "Spot")
  
RETURN  (Avg_SP_price - Avg_FC_price) * Quantity_Spot

Now I have the following problems:

• Incorrect totals - what I want is just the sum of all entries of the list. My formula calculates Avg_SP_price and Avg_FC_price for all Regions (or whatever grouping) at once and multiplies by quantity and that's not the same as adding up all values for the regions. I have seen some videos and comments about this topic but I can't apply the solutions in my case here.
• Basically same issue when I'm selecting multiple Codes - what my formula should do is calculate each saving and adding it up - whereas my formula calculates the average prices for multiple categories at once which is not what I wanted.

Now I thought about the following:

• Creating a new intermediate table, where I calculate all possibilities like the following and use the columns "Region" and "Saving" for my actual report. I'm stuck on how to create a table that changes dynamically the calulated values like the column Avg_FC_Price and so on when I change the Date slicer in report. Is that even possible?
• 
• Using a for loop would be nice. But don't know how. I saw some posts with sumx and earlier, but don't know how to apply it to my issue as I think that would require creating a new table, and then again I can't apply the Date slicer.
• Go with the python integration (but as I read, daily refreshing would only work with a gateway in personal mode, so not an option)

Simple problem, but I'm just stuck and have no idea how to continue. Help very much appreciated 🙂

Thanks and best regards

[v-kelly-msft]

Hi  @Anonymous ,

Calculated table or dimension table cant be dynamic,if you hope it to be dynamic,try a measure table,see below video:

https://www.youtube.com/watch?v=qaqyazlEce0

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak · Accepted solution]

@Anonymous , You need to create a new measure like

new Savings_potential= sumx(summarize(Table, Table[region], Table[Code],"_1",[Savings_potential]),[_1])

A*B should always be a column of row-level calculation(in Measure). If not we need to force a context

also refer:https://www.youtube.com/watch?v=ufHOOLdi_jk

[Anonymous]

Sorry for my late reply. I had to work on another project and just tried it out..

@amitchandak, This is exactly what I was looking for - works perfect. Thanks a lot!!