SUMIF for a measure based on another measure value

[ElenaGrom]

Hello Community,

I am using a table visual like on the screenshot below. The first 5 columns (Customer, Dates and Amount) are taken from different tables. Next, I have a measure "Scenario", which returns a text value. This measure makes a comparison between the 3 dates from the left and asignes a certain "scenario code". In reality the comparison is done based on 6 fields, so the code length is 6 characters.

Also, I am using a date slicer to identify the reporting period. i.e. the fist 5 columns are dynamically updated based on user selection, and therefore the scenario code is recalculated every time.

PROBLEM:

I need to find a way to calculate the subtotal of the amount for each scenario code. In Excel I would do it using the SUMIFS function. In DAX I tried Calculate(SUM....; SUMX but they need a reference to a table or to a column. In my case I need to reference just a value from the measure, as there is no table behind.

Can anyone help how can this be done?

Thank you in advance!

[onurbmiguel_]

Hi ElenaGrom

Did I help you to answer your question? Accepted my post as a solution! 👍

Take a look at the blog: PBI Portugal 

[onurbmiguel_ · Accepted solution]

Hello 

Try to create a column for the scenario: 

Scenario = 
if( 
    'Table'[Reg Date] < 'Table'[Trans Date] , "Y" , "N" 
)
&
if(
    'Table'[End Date]>'Table'[Trans Date] ,"Y", "N"
)

then create the mesaure: 

SubTotal = 
var _scenario = SELECTEDVALUE('Table'[Scenario])
return 
CALCULATE(
    SUM('Table'[Amount]),
    REMOVEFILTERS('Table'[Customer]),
    'Table'[Scenario]=_scenario
    )

Best regards

Bruno Costa | Solution Supplier

Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 👍

Take a look at the blog: PBI Portugal 

[ElenaGrom]

Dear Onurbmiguel,

Many thanks for your quick response.

Sorry for the delay with the answer - i was playing around with the offered solution. Yes, what you suggest works perfectly in the example which I gave in this topic and I could reproduce it at my side. I will mark it as an accepted solution.

My issue is - in my real life case I cannot have the scenario column in the same table where i have customers and amounts, because the scenario value is really dynamic and based on what user selects in slicers - the scenario value may be different for the same data set.

E.g.

Customer1 - in the current example has the value "YY", but he may as well have "YN" etc. 

In reality there is a Scenario table, which says "YY" = Existing Customer, "NY" = New customer etc. But this table cannot have any relation to the main table from which the amounts are analyzed.

This is my fault, I should have asked it differently.  I will open a new topic for that with more details.

Thank you again for your help, your solution solves exactly the problem as it was described!