Advice Needed: Sales Formula from merged data table

Hi DNAers, I am making my way through Sam’s training modules and have perhaps skipped over a vital part of my data model comprehension.

As we all know there are many, many ways to achieve the same outcome with PowerBI; I am looking to find the lowest power formula or advice on restructuring my data model and data table.

Data Sample

Formula Problem for Sales Total (See black circle)
I think I am trying to achieve (when I write words I don’t know how to proceed)
Sales Total = SUMX( 'Sales Merged', [LineTotal] * [LineTotal] ) - (Only one amount of the [Sales.Redemptions])

Current Fears

  1. This is the simplest value that will be used in all other calculations, therefore cannot be complex in nature.
  2. I may require a complete restructure of my model and if so am concerned with understanding how it will work.

Eager to hear what you and the community think of my problem here and appreciate your time reading my hurdle.

This is the model I will be working with if you are wanting to take a ‘deeper dive’
anthonyb.DemoData.pbix (112.9 KB)

Hi @anthonyb

First, I will assume that the correct formula is the one from the PBIX file, meaning:

Sales Total = SUMX( 'Sales Merged', 'Sales Merged'[ItemQty] * 'Sales Merged'[LineTotal] )

So, if Sales.Redemptions containing the same value for each line and you want only one single value from it, the formula could be like this:

Sales Total V2 = CALCULATE( SUMX('Sales Merged', 'Sales Merged'[ItemQty] * 'Sales Merged'[LineTotal])-MAX('Sales Merged'[Sales.Redemptions]) )

If my asumptions were not correct, please indicate how do you choose “only one amount of the Sales.Redemptions”.

Kind Regards,
Lucian

1 Like

Hi @anthonyb, did the response provided by @Lucian help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Hi @anthonyb, we’ve noticed that no response has been received from you since the 8th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @anthonyb, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!