Get table total as column while having filter condition from related table slicer (ALLSELECTED)

[dogba]

Consider 2 Tables: 

Fact Table: 

Policy	Value
A	100
B	200
C	300
D	400
E	500

Dimension Table: 

Policy	Region
A	US
B	UK
C	EMEA
D	US
E	UK

Both tables related on key - "Policy"

For my table visual, I would like to calculate a simple measure "New Measure" which gives me the total of the entire table as a separate column. As such: 

Policy	Value	New Measure
A	100	1500
B	200	1500
C	300	1500
D	400	1500
E	500	1500

Measure Used: 

New Measure = CALCULATE(SUM('FACT'[Value]), ALLSELECTED('FACT'))

However, I have region (from dimension table) as slicer in my report page. This is somehow breaking the table and returning all rows 

Above screenshot, can see even on selecting EMEA, I get all five records. I have a feeling it is doing some sort of cartesian join when using ALLSELECTED.

Expected Behavior: I would like the region slicer to work but see the table total as a different column. 

How can I achieve this? 

Hopefully, question makes sense - happy to provide more details. Thank you. 

Note: Example has been simplified - original use case has multiple dimension tables and fact table has a few million records. Hence, I would need a scalable solution. 

Pbix: Link

Edit: I would need to have the Policy field in my final visual come from table - FACT (not DIM). The original relationship between two is not on this field hence they're not interchangeable. 

[Irwan · Accepted solution]

hello @dogba 

i might be misunderstood but i am using your DAX but get different result with EMEA.

Have you created a relationship between fact and dim table?

it is one-to-one in my screenshot, but in your original table it most likely one-to-many from dim to fact table.

Hope this will help.

Thank you.

[Anonymous · Accepted solution]

Hi @dogba 
Thank you for reaching out microsoft fabric community forum.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-achippa]

Hi @dogba,

As we haven’t heard back from you, we wanted to kindly follow up to check if the issue is resolved?
If any response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @dogba,

We wanted to kindly follow up to check if the issue is resolved?
If any response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[Ashish_Excel]

Hi,

These images should clarify

[dogba]

Thank you for your response. Your solution works but in my table visual, I need the field Policy to come from FACT table (not DIM). Realise my example may have been a little too simple as my original relationship is not on the policy field but a different category. As such, this would not allow me to use Policy field from the region dimension table. 

This is what I am getting on using Policy from FACT table (note how measure returns row total and not overall total)

Is there any way to achieve this?

[Ashish_Excel]

You are welcome.  That is not the right table to drag it from.  That field should be dragged from the Dim table.

[Irwan · Accepted solution]

hello @dogba 

i might be misunderstood but i am using your DAX but get different result with EMEA.

Have you created a relationship between fact and dim table?

it is one-to-one in my screenshot, but in your original table it most likely one-to-many from dim to fact table.

Hope this will help.

Thank you.

[dogba]

Thank you for your response - can you see my reply above? 
I think my question was unclear - the Policy field must come from FACT table in the final table visual as the original relationship is on a different category field. 
I will update my question but hopefully it is clearer this time - is this possible to achieve?

[Irwan]

hello @dogba 

if you want to filter out a value like you have shown in expected behaviour, your need a value in table visual that has a relationship from your dim tabl.

from your updated question, seems there is no relationship between your sample data (the relationship goes to another column which is not shown in sample data).

please share a sample data that represent your original data.

there might be a way around to achive your requirement (consume Policy from fact tbl).

Thank you.