Division measures but need to work in Count of respondents

[agalbraith]

I normally am able to apply a count measure as a filter for a graph and be able to put equal or greater than 5 or 10, but when I try to do this with this particular data it is not letting me use the dropdown in the filter section to set my parameters. These other graphs only have basic average of a column, or percent of total, or percent of cases measure, not something more complex like dividing across columns. That's why I am thinking the count filter works for those but isn't for this division measure.

Is there a way I can put the filter in the division measure itself? I was trying to but it gives an error that "multiple columns cannot be converted to a scalar value". I don't know how to fix it. I would really need to make sure the division calculations are not being shown when there's less than 5 respondents when slicers on the page are used.

Here is some dummy data. This table connects back to the overall data table by ID as many to one (not sure if that's pertinent to know)

ID	Eligible	Receive	Accept
R1	5	4	3
R2	15	10	6
R3	2	2	2
R4	6	4	1
R5	2	2	2
R6	2	1	1
R7	3	3	2
R8	8	4	1
R9	3	1	1
R10	12	8	5
R11	4	1	1
R12	10	9	7
R13	4	3	3
R14	9	5	3
R15	5	5	5

I would need to do division measures for all 3 of the data columns, for instance accept/receive, and then filter by the count of the ID column so they aren't less than 5.

[Anonymous]

Hi @agalbraith ,

Please refer to my pbix file to see if it helps you.

Create three measures.

Accept/Eligible47.8% =
VAR receive_ =
    CALCULATE ( SUM ( 'Table'[Receive] ), ALL ( 'Table' ) )
VAR elig =
    CALCULATE ( SUM ( 'Table'[Eligible] ), ALL ( 'Table' ) )
VAR _accpet =
    CALCULATE ( SUM ( 'Table'[Accept] ), ALL ( 'Table' ) )
VAR _rows =
    CALCULATE ( COUNT ( 'Table'[ID] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF ( _rows < 5, BLANK (), DIVIDE ( _accpet, elig ) )

Accept/Receive69.4% =
VAR receive_ =
    CALCULATE ( SUM ( 'Table'[Receive] ), ALL ( 'Table' ) )
VAR elig =
    CALCULATE ( SUM ( 'Table'[Eligible] ), ALL ( 'Table' ) )
VAR _accpet =
    CALCULATE ( SUM ( 'Table'[Accept] ), ALL ( 'Table' ) )
VAR _rows =
    CALCULATE ( COUNT ( 'Table'[ID] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF ( _rows < 5, BLANK (), DIVIDE ( _accpet, receive_ ) )

Receive/Eligible68.9% =
VAR receive_ =
    CALCULATE ( SUM ( 'Table'[Receive] ), ALL ( 'Table' ) )
VAR elig =
    CALCULATE ( SUM ( 'Table'[Eligible] ), ALL ( 'Table' ) )
RETURN
    VAR _rows =
        CALCULATE ( COUNT ( 'Table'[ID] ), ALLSELECTED ( 'Table' ) )
    RETURN
        IF ( _rows < 5, BLANK (), DIVIDE ( receive_, elig ) )

If I have misunderstood your meaning, please provide your desired output with more details.

Best Regards

Community Support Team _ Polly

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

[agalbraith]

Polly,

So this is working to blank them out when the response is less than 5 but now the problem is that the calculation does not change when I click on different slicers. It just stays the same overall calculated number rather than switching to the calculation based upon the slicer applied. I am impressed that it blanked out the less than 5 though! Is there any way this can be adjusted so that it functions with the slicers on a page?

[Anonymous]

Hi @agalbraith ,

Please try to use Sync Slicers.

You can sync a slicer and use it on any or all pages in a report.

More details : Sync and use slicers on other pages 

Best Regards

Community Support Team _ Polly

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

[TheoC]

Hi @agalbraith 

Are you able to provide an understanding of the output that you want?  So, what would your ideal output look like?

Thanks heaps!

Theo

[TheoC]

Hi @agalbraith 

I've put the attached PBIX together in hope it aligns to what you are after.

Let me know!

Theo 🙂

[agalbraith]

@TheoC Hmm this does not work because it just eliminates anyone with an ID under 5 it looks like. And I wouldn't want a calculation per respondents. Just an overall of the column, I would like to get the three calculations into a working bar column chart if possible and then have the calculations be regulated to not be less than 5 respondents total when other filters on the page change it. Using the division DAX, power bi will total my columns and divide but then I cannot figure out the next layer of making sure the respondents doesn't fall too low.

So power bi does the calculating on the back end like this based on my division measures:

ID	Eligible	Receive	Accept
R1	5	4	3
R2	15	10	6
R3	2	2	2
R4	6	4	1
R5	2	2	2
R6	2	1	1
R7	3	3	2
R8	8	4	1
R9	3	1	1
R10	12	8	5
R11	4	1	1
R12	10	9	7
R13	4	3	3
R14	9	5	3
R15	5	5	5
Total	90	62	43
	Receive/Eligible	68.9%
	Accept/Receive	69.4%
	Accept/Eligible	47.8%

And I want to display this: