ALLSELECTED OR NOT?

[faldiwan]

Hi all,

I need some help please, I have a simple tabular model.
3 tables Portfolio, Position & Date.

The Portfolio table is pretty much a type 1 dimension, Position is a fact table. 

The visual I have contains data from mainly the portfolio table and 2 measures from the Position and a date selected. There is only one filter from the Portfolio table, where they selected 2 companies out of the list of 5 companies.

Company	Investment	Value	Overall Value
X	A	100	1100
Y	B	1000	1100

This is the data i get and its fine, but the issue is that, I want to be able to keep selecting the 2 companies and if I put an extra filter to select 1 investment, id like to see the overall value stay as 1100 because ive selected the 2 companies.

The DAX I have used for Overall Value = CALCULATE( SUM(Value), REMOVEFILTERS(Portfolio), ALLSELECTED(Portfolio[Company]))

This works fine, when all investments appear, but when filtering down to 1, it only gives me the overall value of the company within the investment. Dont understand why? It looks like the below:

Company	Investment	Value	Overall Value
X	A	100	100

Where I would like to keep seeing 1100 in the overall value.

Any help would be much appreciated.

[faldiwan]

Hi 

I did CALCULATE( SUM(Amont), Removefilters(InvestmentCode), ALLSELECTED(Company))

Still only returns the amount within the company in the investment

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[faldiwan_ar]

Position table:

PortfolioId	TotalValue
1	1000
2	2000
3	3000

Portfolio table:

Id	Company	Investment
1	Test1	Apple
2	Test2	Amazon
3	Test3	Arm

Report:

Company	Investment	Total Value	Overall
Test1	Apple	1000	4000
Test3	Arm	3000	4000

If I use the following day for Overall:
CALCULATE( SUM(TotalValue), REMOVEFILTERS(Portfolio[Investment]), ALLSELECTED(Portfolio[Company])

I get the Report table above, which is great, BUT if i only select "Investment = A" in the filter "Filters on this visual" I only get Overall = 1000, because it looks like its summing within the selected investments.

Model example:

Works when selecting 2 companies and all investments within the companies.

When then selecting only 1 investment, should expect to see 4000, but shows only value within the 1 investment selected, need to see the 4000

[lbendlin]

[faldiwan_ar]

Hi,

Im expecting to see 4000, not 6. 

So ive selected Apple like you did and in the company filters selected Company Test1 & Test2, so i should expect to see the sum of the values for both Test1 & Test2.

Your result is returning all 3 companies, when i only selected 2 in my filter

[lbendlin]

That's a "wash me but don't make me wet"  scenario.  You need to use disconnected tables and measures.

[faldiwan_ar]

Hi,

I was hoping I wouldnt need to remodel, but i assumed that would be the case.

Thanks for helping

[lbendlin]

You need to do  REMOVEFILTERS/ALLSELECTED on the investment as well. And if you have a sort column then you need to include that too etc.