Ignoring filters for one measure

[andrew_hardwick]

I am attempting to calculate a measure to show to cost per country for an application portfolio. The measue is called 'Cost per install' and is defined as below:

Cost per install = CALCULATE(sum('public licensedappslicenses'[scost])/count('public licensedappsdeviceassignments'[deviceid]),'Table'[Column])

Application Name	Support Cost	Installed Devices	Cost per install	Total Cost
Application A	€ 4,850.00	5	€ 970.00	€ 4,850.00
Application B	€ 1,000.00	2	€ 500.00	€ 1,000.00

So looking at Application A, I can see the total cost is €4,850 and 5 PCs have this installed so sharing this cost between the 5 PCs means they each pay €970.

We also want to share this cost out per country, so if I create a table showing me the countries I can see 3 for UK, 1 for NL and 1 for RO. So I now want to filter the table above to show the cost per country. So if I click UK I would ideally have the Installed Devices change to 3, the cost per install stay the same and then the total cost = (cost per install * Installed Devices). However when I filter UK it is also changing cost per install as below.

Application Name	Support Cost	Installed Devices	Cost per install	Total Cost
Application A	€ 4,850.00	3	€ 1,616.67	€ 4,850.00
Application B	€ 1,000.00	2	€ 500.00	€ 1,000.00

I would like the result to show as below:

Application Name	Support Cost	Installed Devices	Cost per install	Total Cost
Application A	€ 4,850.00	3	€ 970.00	€ 2,910.00
Application B	€ 1,000.00	2	€ 500.00	€ 1,000.00

Therefore I need the 'Cost per install' measure to not be affected by filters I apply. I have tried using ALLSELECTED, ALLEXCEPT and various other permutations but they all seem to give the same result.

[andrew_hardwick]

Can anyone help with this? It looks like it should be simple, but I am still struggling to get this working.

[v-shex-msft]

Hi @andrew_hardwick ,

I'd like to suggest you create a new table with locations filed as source of slicer.(not add relationship to original tables) Then you can modify your measure formula to compare with row contents with selected records.

Make measure ignore specific filter

Regards,

Xiaoxin Sheng

[andrew_hardwick]

Hi @v-shex-msft ,

I tried this using the sample pbx file and it seems to give the same result. Did you manage to get this to work by looking at my sample file? If so, can you post how you did this, or send me a working file?

[v-shex-msft]

Hi @andrew_hardwick ,

You can make a pbix file with some test data and upload it to onedrive or google drive and share link here.
Notice: Do mask on sensitive data before share.

Regards,

Xiaoxin Sheng

[v-shex-msft]

HI @andrew_hardwick ,

In my opinion, ALLEXCEPT should suitable for your requirement. It will ignore external filters effect and let formula only filter by fields which defined in function.

Cost per install =
CALCULATE (
    SUM ( 'public licensedappslicenses'[scost] ),
    ALLEXCEPT ( Table, 'Table'[Application Name] )
)
    / CALCULATE (
        COUNT ( 'public licensedappsdeviceassignments'[deviceid] ),
        ALLEXCEPT ( Table, 'Table'[Application Name] )
    )

If above not help, please share some sample data for test.

Regards,

Xiaoxin Sheng

[andrew_hardwick]

Hi @v-shex-msft , I tried your suggestion, but the results come out the same as the formula I already had. I have created a sample file here, that demonstrates the issue. Basically when I click the location slicer, I do not want the 'cost per install' measure to be affected.