SUMX according to user selection

DAX DAX Calculations
1

Hi,
I’m trying to create a measure of SUM my Net Sales and combine with a What-if parameter percentage.
The logic/rule is like this:

  1. I have 2 tables :
    Items: ItemID and ItemGroup
    Sales: ItemmID and Net Sales, relation is ItemID

  2. Beside the usual What-if parameter of a percentage calculation factor, I also have slicer for ItemGroup.

  3. User will need to select this Item Group, can be multiple value

  4. Then I will display it in a table, contain Item Group, Net sales (original), and the Scenario sales, The net sales will be the Sum value of all the original Net sales whereby Scenario Sales, for those selected Item group, it will calculate Sum of Net sales with addition of percentage What-if, the non selected will remain as the original Net sales.

  5. The table interactions, I will put to “not” filtering, so even though user select 2 or 3 Item group, the table will display all item group (with no 4. rule)

How would be the Scenario sales measure looks like ?

Kindly help.
Thanks

2

Hello @Toni,

Thank You for posting your query onto the Forum.

This similar type of technique has already been showcased by Sam Sir in lots of his videos pertaining to the topics - “Scenario Analysis”. I’m providing few of the links of the videos available onto our education portal which covers this type of analysis. Also please feel free to browse our education portal for more videos on this type of analysis and concepts.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

1 Like
3

Hi @Harsh,

Yes, I saw the tutorial but I think it is a bit different because what Sam did is to manipulate the Unit price in the Product table, whereby mine didn’t have any unit price. So instead re-calculate using SUMX of the unit price and quantity, mine is still use the “net sales” of the transaction table (Sales).

I tried to followed the Scenario sales like this :

>    Scenario sales = 
> VAR ScenarioSales = SUMX(
>                         'Sales',
>                         [Net sales] * (1 + [% Scenario])
> )
> RETURN CALCULATE(ScenarioSales,
>                 ALLSELECTED(Items[Item group])
> )

but in my table consist of all the item group with Net sales and Scenario sales, all of the item group in Scenario sales column is still effected by the [% Scenario] value, although I only choose 2 of them, by mean I expected only 2 item groups displayed with the effected parameter.

image
image770×315 19.6 KB

4

@Toni There are a couple of things wrong here:

  1. Context transition on a fact table
  2. Variable evaluated outside/before CALCULATE being evaluated again inside CALCULATE, which by the way has no impact on a variable as variables are constants and you can’t modify their values
5

Hi @AntrikshSharma,

I’m sorry, a bit lost on both of the subjects. Maybe some explanation over here on what should I do ?

Basically as described in my 1st post, I don’t have unit price in Item table so there is nothing for me to re-calculate in item level like Sam’s did.

And the filter to choose which item will be effected, is not the item it self but the item group which is inside of the item table. And the only value that need to be “modified” is the “net sales” in my fact table, I guess this is the one you’re mentioned in point no 1. But I don’t know what’s wrong with it ?

Thanks,

Thanks,

6

Hi,

The other day, I also tried like this, but it is still give me all of the item group being effected by What-if parameter :

> Scenario sales = SUMX('Sales',
>                                      IF (NOT (RELATED(Items[Itemgroup]) in ALLSELECTED(Items[Itemgroup])),
>                                           'Sales'[Net sales],
>                                           'Sales'[Net sales] * (1 + [% Scenario]))
>                                      )

In my mind, that SUMX will check first whether the item group of the item is within the selected item group. If not then just give me [Net sales] but if within selected item group, calculate again the Net sales with What-if factor.

However it is wrong.

Please help to understand what is wrong and how to resolve it.

Thanks,

7

@Toni Both of those are complex topics, and are pretty much out of the scope of these short comment based conversation.

  1. But in short don’t call a measure in a row context if the table contains duplicate values, context transition should be used on Dimensions or once you extract unique values of a column/table

  2. Variables are evaluated where they are defined, they are constants, you can’t DEFINE a variable outside CALCULATE and then modify its value inside CALCULATE

8

Hi @Toni, we’ve noticed that no response has been received from you since the 22nd of February. 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.

9

Hi @Toni, 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!