Slicer with two columns to only filter element by one

[Anonymous]

Hi there,

I current have a slicer with two columns - 'Business', 'Project'. This means the slicer lets the user pick which project in a particular business to show details for. One of the details I show is the cost of the project.

I am now working on getting a datacard show the summed cost of all the projects within the selected business (so the user can see how significant the selected project is in a wider context). I am working on a measure, but I am struggling.

This is because when the slicer interacts with the datacard, the selected project is also filtering to the specific project selected. Instead, I want the project ignored and just the business selected.

I have tried coding it in DAX, but I can't seem to get it to work.

AllBusiness = SUMX(ALL(Table[Projectname]), SUM(Table[ProjectCost]))

This is returning a number far too large.

I have quite a few other context filters, on the report/page that need to be considered as well, so the straight 'ALL' doesn't work.

Thank you.

[v-xiaotang]

Hi @Anonymous 

Try this measure,

AllBusiness =
CALCULATE (
    SUM ( Table[ProjectCost] ),
    FILTER (
        ALL ( Table ),
        Table[Projectname] = SELECTEDVALUE ( slicer[Projectname] )
    )
)

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi there,

How do you get the 'slicer' to work? 

[v-xiaotang]

Hi @Anonymous 

Thanks for your reply. If the measure doesn't work, could you share some sample data and the screenshot of slicer? Thanks. ( Otherwise we are just guessing....)

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

I found 

CALCULATE(

DISTINCTCOUNT(

Table[Project]

),

ALLEXCEPT(

Table,

Table[Busines],

Table[Cat]

)

to work.

[jsaunders_zero9]

Hi @Anonymous 

Does this help?

ProjectCosts = SUM(Projects[ProjectCost])
TotalProjectCosts = CALCULATE(Projects[ProjectCosts],ALL(Projects[Project]))

[jsaunders_zero9]

In your example it might look more like 

AllBusiness = CALCULATE(SUM(Table[ProjectCost]),ALL(Table[Projectname]))

[Anonymous]

Hi there,

I feel like this is what it should be too, however the resulting value of this is still far larger than the total cost of the business. 

[jsaunders_zero9]

Are you filtering on the business also (just noticed this in your first post)? Is it possible that the ALL() is causing the total to be a sum of all projects for all businesses? have you tried ALLEXCEPT?

[Anonymous]

I have tried all excpet, but there is about 5 other filters on the page that have to be included so I'm not entirely sure I've done it correctly.

I'll have a play around with it now.