Conditional Subtotal Measure

[Taylan]

Hi - I have a table as below. I need to calculate the Total Cost of Main Supplier (dynamic) for corresponding styles of Alternative Suppliers only. so

Measure should run for "Alternative 1" 4+5=9

Measure should run for "Alternative 2" 4 only.

Trying to achieve second screenshot.I have a measure returning the main supplier name for style and Current Cost Price (main supplier's cost price). These might be used.

As solution I was trying to create a list of styles where Alternatives offered (like for Alternative 2 only x) under var and then filter the the main table filtered for Main Supplier and also for this dynamic list created but couldn't manage.

Can you please help me to overcome this issue?

One thing the tool is in Power Pivot so I have some limitations like IN cannot be used or not create a seperate table in data model as far as I understand.

[Taylan · Accepted solution]

Hi - I solved my problem with intersect formula and use the calculated meaure with containsrow for getting desired subtotal...  

[amitchandak]

@Taylan , is the data you have shared in the first table is your source data?

if not can you share source data

[Taylan]

Hi @amitchandak  - the first table is the source of data. actually it is a simplified version of a complex queries collecting data from different sheets of different files in folder. I have a supplier column and sheetname column (as supplier name or "Existing") The table below is reflects better the result of those queries. I created measure to find the main supplier which I can filter the table against.(database is the table name)

Main Supplier=

var rslt=CALCULATE(MAX(Database[Supplier]),Database[SheetName]="EXISTING")

return

IF(HASONEFILTER(Database[STYLE]),IF(rslt="","NA",rslt))

a filter example:

Main Supplier Cost Price

=IF(HASONEFILTER(Database[STYLE]),

CALCULATE(MAX(Database[Cost Price),Database[SheetName]="EXISTING"))

there are many columns with similar names in my actual file that might confuse you but if you still need I can work on to make a bit clearer and send it; please let me know. Thanks in advance