Create measure using secondary slicer

[halfcanadian]

I have a page that has a parameter in a slicer with 4 dimensions. The 4 dimensions all have the same value ('Recevied Funding', 'Did not Receive Funding'). I've created a second slicer off of this, which shows the value of the selected dimension.

I also have 3 additional slicers that are not directly connected to the parameter.

My data has a field (customer count) that has the number of individuals who did, or did not, receive funding in the 4 categories, as well as the various values for the 3 additional variables (Demographic factors, so Sex, Race, Age).

I want to create a measure that shows the percent of customers that received funding for the selected parameter value, and any demographic variables that have been selected. So a numerator that accepts all filters, and a denominator that accepts all filters EXCEPT for the "Received Funding" splicer for the parameter variables.

[amitchandak]

@halfcanadian , If the table are connected simple count of sum measure will do , else use treatas for passing values

SUM('Table'[customer count])

or

CALCULATE(
SUM('Table'[customer count]),
'Table'[Parameter] = "Received Funding"
)

Denominator can be

CALCULATE(
SUM('YourTable'[customer count]),
REMOVEFILTERS('Table'[Parameter])
)

or

CALCULATE(
SUM('YourTable'[customer count]),
filter(all('Table'), 'Table'[Parameter] <>"Received Funding" )
)

Add filter for other parameters if needed

[halfcanadian]

These give me a value of 100% across the board.

The parameter is in a table generated by Power BI that is not connected to the main data table.

When I try to implement the last suggestion (filter(all('Table'), 'Table'[Parameter] <> "Received Funding"), this generates an error (cannot find name).

[halfcanadian]

Here's what I wrote:

Denominator = CALCULATE(

SUM('SI'[Customer Count]),

filter(all('SI'), 'Funding Sources'[Funding Sources] <>"2. Received Funding" )

)

The message I received:

A single value for column 'Funding Sources' in table 'Funding Sources' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[v-yiruan-msft]

Hi @halfcanadian ,

Please update the formula of measure [Denominator] as below and check if it can return the expected result...

Denominator =
CALCULATE (
    SUM ( 'SI'[Customer Count] ),
    FILTER (
        ALL ( 'Funding Sources' ),
        'Funding Sources'[Funding Sources] <> "2. Received Funding"
    )
)

If the above one can't help you get the expected result, please provide some raw data in your tables 'SI' and 'Funding Sources' (exclude sensitive data) with Text format and your visual settings. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[halfcanadian]

Here's a link to a PBI file that aproximates the problem.

https://www.dropbox.com/scl/fi/dvkpo9slubpkx7ifc954n/Filter-Question.pbix?rlkey=kzhkjurft76r7bbijnvx...