Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
geiratatea
Frequent Visitor

Creating two virtual tables from one target table and some measures

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 )
)

 

geiratatea_1-1673915191411.png

 

 

geiratatea_0-1673915136784.png

 

 

 

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.