Creating two virtual tables from one target table and some measures

[geiratatea]

Hi!

I have a challenge that I really want to solve. It is like this.

Target table contains a set of targets with certain values. Each Target has an investment cost and a npv. Targets can be combined to get the highest sum of npv but it cannot exceed the budget limit here set to 4 000 000.

Based on this rule, a set of permutations must be checked. Here all targets on their own is lower than the limit. In combinations there could be a lot more. This combination table is the TargetPool.

Could this table be created by a virtual table construction using DAX? A valid combination of targets creates a portfolio ID => P{#}. The Portfolio table could of course also be extracted out of TargetPool making another virtual Table. Then you only have one actual Table and two virtual tables (loosing the relationship).

But if any of you is able to create this and also can reuse these two measures, I would be grateful

Total_PortfolioNPV = CALCULATE([TotalNPV],TargetPool)

RankMeasure =

CALCULATE (

    RANKX ( ALLSELECTED ( 'Portfolio'), CALCULATE([Total_PortfolioNPV],ALLEXCEPT(Portfolio,Portfolio[PortfolioID])),, DESC, DENSE )

)

[v-shex-msft]

HI @geiratatea,

Did you mean to setting a condition on bottom level summarized values to prevent them above the 4M(this condition will be ignored when expressions work on the total levels), right?

If that is the case, you can refer to the Greg’s blog to use summarize function in a variable to summary value with their groups , then you can use iterator function sumx to apply the second level aggregated with if statement to check and replace these over target values.

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng