VALUES() equivalent for multiple selection in a slicer

[gLB]

Hi,

I am currently working on a portfolio analysis model where I need to do a specific operation on multiple selected deals.

My goal here is to be able to run the CALCULATE() on more than one selection. Currently I can do it with one selection in my slicer using the VALUES() formula. It doesn't look like VALUES() can handle more than one selection because I keep getting the error: " A table of multiple values was supplied where value was expected"

In the filter of my CALCULATE I want the DimDeals table to find the selected deals and then do the calculation only for those selected deals. What should I do to be able to CALCULATE on more than one selection? Can it be achieved in Power BI?

See below a quick example of what I am trying to do:

Sensies_MultipleDEALS =

CALCULATE(SUM([NCF]),
DimDeals[DealName]=VALUES(DimDeals_Sensies[DealName_Sensies]))

where DimDeals_Sensies is a table with all the name of the deals. I want to be able to select more than one deal.

The "real" formula is longer than that. The other deals that are not selected with have their own calculation.

DimDeals_Sensies slicer

Deal 1 Selected

Deal 2

Deal 3

Deal 4 Selected

Deal 5

By selecting the Deal 1 and the Deal 4 my measure should return the sum of the NCF only for those deals.

Thank you!

[v-shex-msft]

Hi @gLB,

You can try to use the source table of slicer as the filter on the measure.

For example:

Sensies_MultipleDEALS  = CALCULATE(SUM([NCF]),FILTER(ALL(DimDeals),CONTAINS(ALLSELECTED(DimDeals_Sensies),DimDeals_Sensies[DealName_Sensies],DimDeals[DealName])))

Regards,

Xiaoxin Sheng

[gLB]

Hey Xiaoxin,

Thanks for the swift reply.

It looks like the CONTAINS is good to identify more than one selection in a slicer but there is a probem.

Instead of displaying the sum of each selected deals individually, it is displaying the sum of both deals on every deals. Example below:

Deal 1 - Selected        sum of cash flows is 2,000

Deal 2 - Not Selected

Deal 3 - Not Selected

Deal 4 - Selected       sum of cash flows is 3,000

Deal 5 - Not Selected

What is currently displayed using your formula:

Deal 1 - 5,000

Deal 2 - 5,000

Deal 3 - 5,000

Deal 4 - 5,000

Deal 5 - 5,000

What I would like to get:

Deal 1 - 2,000

Deal 2 - 0

Deal 3 - 0

Deal 4 - 3,000

Deal 5 - 0

I really need the non-selected deals to be displayed because my next step is to do a different calculation on the non-selected deals vs selected deals.

Please let me know if you need more info to help me on this matter.

Thank you for your help!

[CheenuSing]

Hi @gLB

Is it possible to share the data and the output expected. Please load the dat and requirement to one drive and share the link.

Cheers

CheenuSing

[gLB]

Hi CheenuSing,

Unfortunately I can't share the data because my question is related to my job and the company I work for doesn't allow to share data outside the corp.

Is there any other way I could give you more details to exactly explain what I would like my Power BI to do?

Thank you

[TomMartens]

Hey,

sharing your data, does not necessarily mean, put your company into the hands of us data afficinados 😉

It can also mean, honor the time of all the great community members who are willing to help and create " fake sample data", but be precise enough that your data is able to support your requirement.

If your not allowed to use a public cloud like onedrive or dropbox, cust copy your fake data from Excel into the "Insert Code" object.

Regards

Tom

[gLB]

CashFlows table		
Deal_Index	Date	 Cashflow 
1	1/31/2014	 (1,000,000)
1	2/28/2014	 - 
1	3/31/2014	 - 
1	4/30/2014	 - 
1	5/31/2014	 - 
1	6/30/2014	 - 
1	7/31/2014	 - 
1	8/31/2014	 - 
1	9/30/2014	 - 
1	10/31/2014	 - 
1	11/30/2014	 - 
1	12/31/2014	 - 
1	1/31/2015	 - 
1	2/28/2015	 - 
1	3/31/2015	 100,000 
1	4/30/2015	 - 
1	5/31/2015	 - 
1	6/30/2015	 - 
1	7/31/2015	
1	8/31/2015	 - 
1	9/30/2015	 200,000 
1	10/31/2015	 - 
1	11/30/2015	 - 
1	12/31/2015	 - 
1	1/31/2016	 - 
1	2/29/2016	 - 
1	3/31/2016	 - 
1	4/30/2016	 - 
1	5/31/2016	 - 
1	6/30/2016	 400,000 
1	7/31/2016	 - 
1	8/31/2016	 - 
1	9/30/2016	 - 
1	10/31/2016	 - 
1	11/30/2016	 - 
1	12/31/2016	 - 
1	1/31/2017	 900,000 
2	1/31/2014	 (1,250,000)
2	2/28/2014	 - 
2	3/31/2014	 - 
2	4/30/2014	 - 
2	5/31/2014	 - 
2	6/30/2014	 - 
2	7/31/2014	 - 
2	8/31/2014	 - 
2	9/30/2014	 - 
2	10/31/2014	 - 
2	11/30/2014	 - 
2	12/31/2014	 - 
2	1/31/2015	 - 
2	2/28/2015	 - 
2	3/31/2015	 125,000 
2	4/30/2015	 - 
2	5/31/2015	 - 
2	6/30/2015	 - 
2	7/31/2015	 - 
2	8/31/2015	 - 
2	9/30/2015	 250,000 
2	10/31/2015	 - 
2	11/30/2015	 - 
2	12/31/2015	 - 
2	1/31/2016	 - 
2	2/29/2016	 - 
2	3/31/2016	 - 
2	4/30/2016	 - 
2	5/31/2016	 - 
2	6/30/2016	 500,000 
2	7/31/2016	 - 
2	8/31/2016	 - 
2	9/30/2016	 - 
2	10/31/2016	 - 
2	11/30/2016	 - 
2	12/31/2016	 - 
2	1/31/2017	 1,125,000 
3	1/31/2014	 (1,562,500)
3	2/28/2014	 - 
3	3/31/2014	 - 
3	4/30/2014	 - 
3	5/31/2014	 - 
3	6/30/2014	 - 
3	7/31/2014	 - 
3	8/31/2014	 - 
3	9/30/2014	 - 
3	10/31/2014	 - 
3	11/30/2014	 - 
3	12/31/2014	 - 
3	1/31/2015	 - 
3	2/28/2015	 - 
3	3/31/2015	 156,250 
3	4/30/2015	 - 
3	5/31/2015	 - 
3	6/30/2015	 - 
3	7/31/2015	 - 
3	8/31/2015	 - 
3	9/30/2015	 312,500 
3	10/31/2015	 - 
3	11/30/2015	 - 
3	12/31/2015	 - 
3	1/31/2016	 - 
3	2/29/2016	 - 
3	3/31/2016	 - 
3	4/30/2016	 - 
3	5/31/2016	 - 
3	6/30/2016	 625,000 
3	7/31/2016	 - 
3	8/31/2016	 - 
3	9/30/2016	 - 
3	10/31/2016	 - 
3	11/30/2016	 - 
3	12/31/2016	 - 
3	1/31/2017	 1,406,250 
4	1/31/2014	 (1,953,125)
4	2/28/2014	 - 
4	3/31/2014	 - 
4	4/30/2014	 - 
4	5/31/2014	 - 
4	6/30/2014	 - 
4	7/31/2014	 - 
4	8/31/2014	 - 
4	9/30/2014	 - 
4	10/31/2014	 - 
4	11/30/2014	 - 
4	12/31/2014	 - 
4	1/31/2015	 - 
4	2/28/2015	 - 
4	3/31/2015	 195,313 
4	4/30/2015	 - 
4	5/31/2015	 - 
4	6/30/2015	 - 
4	7/31/2015	 - 
4	8/31/2015	 - 
4	9/30/2015	 390,625 
4	10/31/2015	 - 
4	11/30/2015	 - 
4	12/31/2015	 - 
4	1/31/2016	 - 
4	2/29/2016	 - 
4	3/31/2016	 - 
4	4/30/2016	 - 
4	5/31/2016	 - 
4	6/30/2016	 781,250 
4	7/31/2016	 - 
4	8/31/2016	 - 
4	9/30/2016	 - 
4	10/31/2016	 - 
4	11/30/2016	 - 
4	12/31/2016	 - 
4	1/31/2017	 1,757,813 
5	1/31/2014	 (2,441,406)
5	2/28/2014	 - 
5	3/31/2014	 - 
5	4/30/2014	 - 
5	5/31/2014	 - 
5	6/30/2014	 - 
5	7/31/2014	 - 
5	8/31/2014	 - 
5	9/30/2014	 - 
5	10/31/2014	 - 
5	11/30/2014	 - 
5	12/31/2014	 - 
5	1/31/2015	 - 
5	2/28/2015	 - 
5	3/31/2015	 244,141 
5	4/30/2015	 - 
5	5/31/2015	 - 
5	6/30/2015	 - 
5	7/31/2015	 - 
5	8/31/2015	 - 
5	9/30/2015	 488,281 
5	10/31/2015	 - 
5	11/30/2015	 - 
5	12/31/2015	 - 
5	1/31/2016	 - 
5	2/29/2016	 - 
5	3/31/2016	 - 
5	4/30/2016	 - 
5	5/31/2016	 - 
5	6/30/2016	 976,563 
5	7/31/2016	 - 
5	8/31/2016	 - 
5	9/30/2016	 - 
5	10/31/2016	 - 
5	11/30/2016	 - 
5	12/31/2016	 - 
5	1/31/2017	 2,197,266 
6	1/31/2014	 (3,051,758)
6	2/28/2014	 - 
6	3/31/2014	 - 
6	4/30/2014	 - 
6	5/31/2014	 - 
6	6/30/2014	 - 
6	7/31/2014	 - 
6	8/31/2014	 - 
6	9/30/2014	 - 
6	10/31/2014	 - 
6	11/30/2014	 - 
6	12/31/2014	 - 
6	1/31/2015	 - 
6	2/28/2015	 - 
6	3/31/2015	 305,176 
6	4/30/2015	 - 
6	5/31/2015	 - 
6	6/30/2015	 - 
6	7/31/2015	 - 
6	8/31/2015	 - 
6	9/30/2015	 610,352 
6	10/31/2015	 - 
6	11/30/2015	 - 
6	12/31/2015	 - 
6	1/31/2016	 - 
6	2/29/2016	 - 
6	3/31/2016	 - 
6	4/30/2016	 - 
6	5/31/2016	 - 
6	6/30/2016	 1,220,703 
6	7/31/2016	 - 
6	8/31/2016	 - 
6	9/30/2016	 - 
6	10/31/2016	 - 
6	11/30/2016	 - 
6	12/31/2016	 - 
6	1/31/2017	 2,746,582 
7	1/31/2014	 (3,814,697)
7	2/28/2014	 - 
7	3/31/2014	 - 
7	4/30/2014	 - 
7	5/31/2014	 - 
7	6/30/2014	 - 
7	7/31/2014	 - 
7	8/31/2014	 - 
7	9/30/2014	 - 
7	10/31/2014	 - 
7	11/30/2014	 - 
7	12/31/2014	 - 
7	1/31/2015	 - 
7	2/28/2015	 - 
7	3/31/2015	 381,470 
7	4/30/2015	 - 
7	5/31/2015	 - 
7	6/30/2015	 - 
7	7/31/2015	 - 
7	8/31/2015	 - 
7	9/30/2015	 762,939 
7	10/31/2015	 - 
7	11/30/2015	 - 
7	12/31/2015	 - 
7	1/31/2016	 - 
7	2/29/2016	 - 
7	3/31/2016	 - 
7	4/30/2016	 - 
7	5/31/2016	 - 
7	6/30/2016	 1,525,879 
7	7/31/2016	 - 
7	8/31/2016	 - 
7	9/30/2016	 - 
7	10/31/2016	 - 
7	11/30/2016	 - 
7	12/31/2016	 - 
7	1/31/2017	 3,433,228 
8	1/31/2014	 (4,768,372)
8	2/28/2014	 - 
8	3/31/2014	 - 
8	4/30/2014	 - 
8	5/31/2014	 - 
8	6/30/2014	 - 
8	7/31/2014	 - 
8	8/31/2014	 - 
8	9/30/2014	 - 
8	10/31/2014	 - 
8	11/30/2014	 - 
8	12/31/2014	 - 
8	1/31/2015	 - 
8	2/28/2015	 - 
8	3/31/2015	 476,837 
8	4/30/2015	 - 
8	5/31/2015	 - 
8	6/30/2015	 - 
8	7/31/2015	 - 
8	8/31/2015	 - 
8	9/30/2015	 953,674 
8	10/31/2015	 - 
8	11/30/2015	 - 
8	12/31/2015	 - 
8	1/31/2016	 - 
8	2/29/2016	 - 
8	3/31/2016	 - 
8	4/30/2016	 - 
8	5/31/2016	 - 
8	6/30/2016	 1,907,349 
8	7/31/2016	 - 
8	8/31/2016	 - 
8	9/30/2016	 - 
8	10/31/2016	 - 
8	11/30/2016	 - 
8	12/31/2016	 - 
8	1/31/2017	 4,291,534 
9	1/31/2014	 (5,960,464)
9	2/28/2014	 - 
9	3/31/2014	 - 
9	4/30/2014	 - 
9	5/31/2014	 - 
9	6/30/2014	 - 
9	7/31/2014	 - 
9	8/31/2014	 - 
9	9/30/2014	 - 
9	10/31/2014	 - 
9	11/30/2014	 - 
9	12/31/2014	 - 
9	1/31/2015	 - 
9	2/28/2015	 - 
9	3/31/2015	 596,046 
9	4/30/2015	 - 
9	5/31/2015	 - 
9	6/30/2015	 - 
9	7/31/2015	 - 
9	8/31/2015	 - 
9	9/30/2015	 1,192,093 
9	10/31/2015	 - 
9	11/30/2015	 - 
9	12/31/2015	 - 
9	1/31/2016	 - 
9	2/29/2016	 - 
9	3/31/2016	 - 
9	4/30/2016	 - 
9	5/31/2016	 - 
9	6/30/2016	 2,384,186 
9	7/31/2016	 - 
9	8/31/2016	 - 
9	9/30/2016	 - 
9	10/31/2016	 - 
9	11/30/2016	 - 
9	12/31/2016	 - 
9	1/31/2017	 5,364,418 
10	1/31/2014	 (7,450,581)
10	2/28/2014	 - 
10	3/31/2014	 - 
10	4/30/2014	 - 
10	5/31/2014	 - 
10	6/30/2014	 - 
10	7/31/2014	 - 
10	8/31/2014	 - 
10	9/30/2014	 - 
10	10/31/2014	 - 
10	11/30/2014	 - 
10	12/31/2014	 - 
10	1/31/2015	 - 
10	2/28/2015	 - 
10	3/31/2015	 745,058 
10	4/30/2015	 - 
10	5/31/2015	 - 
10	6/30/2015	 - 
10	7/31/2015	 - 
10	8/31/2015	 - 
10	9/30/2015	 1,490,116 
10	10/31/2015	 - 
10	11/30/2015	 - 
10	12/31/2015	 - 
10	1/31/2016	 - 
10	2/29/2016	 - 
10	3/31/2016	 - 
10	4/30/2016	 - 
10	5/31/2016	 - 
10	6/30/2016	 2,980,232 
10	7/31/2016	 - 
10	8/31/2016	 - 
10	9/30/2016	 - 
10	10/31/2016	 - 
10	11/30/2016	 - 
10	12/31/2016	 - 
10	1/31/2017	 6,705,523 

DimDeals table	
Deal_Index	DealName
1	Deal_1
2	Deal_2
3	Deal_3
4	Deal_4
5	Deal_5
6	Deal_6
7	Deal_7
8	Deal_8
9	Deal_9
10	Deal_10

DimDeals_Loss	
Index_Loss	DealName_Loss
1	Deal_1
2	Deal_2
3	Deal_3
4	Deal_4
5	Deal_5
6	Deal_6
7	Deal_7
8	Deal_8
9	Deal_9
10	Deal_10

Date_Loss table
Date
1/31/2014
2/28/2014
3/31/2014
4/30/2014
5/31/2014
6/30/2014
7/31/2014
8/31/2014
9/30/2014
10/31/2014
11/30/2014
12/31/2014
1/31/2015
2/28/2015
3/31/2015
4/30/2015
5/31/2015
6/30/2015
7/31/2015
8/31/2015
9/30/2015
10/31/2015
11/30/2015
12/31/2015
1/31/2016
2/29/2016
3/31/2016
4/30/2016
5/31/2016
6/30/2016
7/31/2016
8/31/2016
9/30/2016
10/31/2016
11/30/2016
12/31/2016
1/31/2017

Loss table
Loss
0%
25%
50%
75%
100%

The goal of my measure is to simulate a future loss (Loss table) of the future cash flows after a specific date (Month-Year_Loss) for specific deals (DimDeals_Loss) in a portfolio and see the impact on the whole portfolio performance.

See below at the bottom of the post the expected results if I select Deal_3 and Deal_6 (multiple selection) in the DimDeals_Loss, select 9/30/2016 as the Date in Date_Loss table and if I select 50% Loss in the Loss table.

The results of the measure for the Deals EXCEPT Deal_3 and Deal_6 should simply be the sum of the cash flows of all the periods.

The results of the measure the Deal_3 and Deal_6 should be the sum of the cash flows prior to 9/30/2016 and HALF (50%) of the sum of the cash flows after 9/30/2016. 

Portfolio_Deals_Loss_Analysis =

CALCULATE(SUM(CashFlows[CashFlow]),CashFlows[Date]<=VALUES(Date_Loss[Date]),

+

CALCULATE(SUM(CashFlows[CashFlow])*(1-VALUES(Loss[Loss])),

DimDeals[DealName]=VALUES(DimDeals_Loss[DealName_Loss]),

FILTER(CashFlows, CashFlows[Date]>VALUES(Date_Loss[Date])))

+

CALCULATE(SUM(CashFlows[CashFlow]),

DimDeals[DealName]<>VALUES(DimDeals_Loss[DealName_Loss]),

FILTER(CashFlows, CashFlows[Date]>VALUES(Date_Loss[Date])))

Expected results using the fake data sample:

Deal_1 600,000

Deal_2 750,000

Deal_3 234,375

Deal_4 1,171,875

Deal_5 1,464,844

Deal_6 457,764

Deal_7 2,288,818

Deal_8 2,861,023

Deal_9 3,576,279

Deal_10 4,470,348

Please let me know if this was clear enough and if the fake data sample inserted via the "Insert Code" object is OK. 

Thanks a lot for your help guys. I appreciate it.

gLB

[v-shex-msft · Accepted solution]

Hi @gLB,

I modify your formula, perhaps you can try it if suitable for your requirement.

Calculate column:

DealName = LOOKUPVALUE(DimDeals[DealName],[Deal_Index],[Deal_Index]) 

Measures:

Date_Loss_Select = IF(HASONEVALUE(Date_Loss[Date]),VALUES(Date_Loss[Date]),BLANK()) 

Loss_Select = IF(HASONEVALUE(Loss[Loss]),VALUES(Loss[Loss]),BLANK()) 

Before Loss Date = 
CALCULATE(SUM(CashFlows[CashFlow]),FILTER(ALL(CashFlows),[Deal_Index]=MAX([Deal_Index])&&CashFlows[Date]<=[Date_Loss_Select]))

After Loss Date = 
CALCULATE(SUM(CashFlows[CashFlow])*(1-[Loss_Select]),
FILTER(ALLSELECTED(CashFlows),CONTAINS(ALLSELECTED(DimDeals),DimDeals[Deal_Index],CashFlows[Deal_Index])),
FILTER(ALL(CashFlows),[Deal_Index]=MAX([Deal_Index])&&CashFlows[Date]>[Date_Loss_Select]))

Except Selected Loss = 
CALCULATE(SUM(CashFlows[CashFlow]),
FILTER(ALL(CashFlows),NOT(CONTAINS(DimDeals_Loss,DimDeals_Loss[Index_Loss],CashFlows[Deal_Index]))),
FILTER(ALL(CashFlows),[Deal_Index]=MAX([Deal_Index])&&CashFlows[Date]>[Date_Loss_Select]))

Portfolio_Deals_Loss_Analysis = 
[Before Loss Date]+[After Loss Date]+[Except Selected Loss]

Result:

Regards,

Xiaoxin Sheng

[gLB]

Hey @v-shex-msft,

That did exactly what I wanted to do, you are a Power BI genius! 

I did one small tweak. See below in bold. This way only the selected deals that I want to analyse with a loss get the loss calculation. Wth that the sum make sense for each deal.

After Loss Date = 
CALCULATE(SUM(CashFlows[CashFlow])*(1-[Loss_Select]),
FILTER(ALLSELECTED(CashFlows),CONTAINS(ALLSELECTED(DimDeals_Loss),DimDeals_Loss[Index_Loss],CashFlows[Deal_Index])),
FILTER(ALL(CashFlows),[Deal_Index]=MAX([Deal_Index])&&CashFlows[Date]>[Date_Loss_Select]))

My next step is to calculate a XIRR on the DealName_Loss deals to get the final XIRR impact on the portfolio while also having the individual deals XIRR.

I'll let you know if I'm also having problem with this one...

Again, thanks a lot for your help! I really appreciate it.

gLB

[gLB]

The expected results of the XIRR with the sample posted earlier would be as follows.

Deal_1 20.32%

Deal_2 20.32%

Deal_3 6.01%

Deal_4 20.32%

Deal_5 20.32%

Deal_6 6.01%

Deal_7 20.32%

Deal_8 20.32%

Deal_9 20.32%

Deal_10 20.32%

Thanks,

gLB

[gLB]

Hi @v-shex-msft I have been trying to create a measure to add a column with the XIRR of the cash flows like I mentionned in my previous post. I am unable to get something out of it. The formula is different because you have to work directly with columns (values and dates) instead of measures. 

Is there any way we could convert the Portfolio_Deals_Loss_Analysis into a temporary column to the table and then calculate XIRR on it?

I really don't know how to approach the XIRR scenario instead of the sum of cash flows scenario because it has a lot of steps.

Thanks!

gLB

[v-shex-msft]

HI @gLB,

>>The formula is different because you have to work directly with columns (values and dates) instead of measures.
Current power bi not support to create a dynamic calculate column/table based on chosen of slicer.

Notice: slicer works on data view level(visual/measure), calculate column/table store at data model level, so slicer won't affect the calculate column/table.

Regards,

Xiaoxin Sheng

[gLB]

Hi @v-shex-msft!

Got it. Thanks for your swift reply!

gLB

[gLB]

Got it. I will create a fake sample of data with the desired outputs and find a way to share it with you.

I should be able to send someting tomorrow.

Thanks a lot for your help on this,

gLB