Count of unique filtered by a variable

[Anonymous]

Hello all, 

I have been trying to do a user count, so I can use that number and divide it by total work. My goal is to have an average per selected group. The Measure 'Variable" is giving me the correct total user amount.

the formula for variable:

VARIABLE = 

var Totaluser = [(CF count - Preparer)]+[(CF count - Reviewer)]+[(Recon count - Preparer)]+[(Recon count - Reviewer)]+[(PortCo count - Preparer)]+[(PortCo count - Reviewer)]

var TotalSUM = 
IF(Totaluser=BLANK(),BLANK(),
CALCULATE(count(CF_NAV_Preparer[Operation Type]),ALLSELECTED(CF_NAV_Preparer),
CF_NAV_Preparer[Operation Type]="Cash Flow"||
CF_NAV_Preparer[Operation Type]="Stock Sale"||
CF_NAV_Preparer[Operation Type]="Stock Distribution")
+
CALCULATE(count(CF_NAV_Preparer[Recon Entered By - 2]),ALLSELECTED(CF_NAV_Preparer),
CF_NAV_Preparer[Operation Type]="Valuation")
+
CALCULATE(count(CF_NAV_Reviewer [Operation Type]),ALLSELECTED(CF_NAV_Reviewer),
CF_NAV_Reviewer[Operation Type]="Cash Flow"||
CF_NAV_Reviewer[Operation Type]="Stock Sale"||
CF_NAV_Reviewer[Operation Type]="Stock Distribution")
+
CALCULATE(count(CF_NAV_Reviewer[Recon Entered By - 2]),ALLSELECTED(CF_NAV_Reviewer),
CF_NAV_Reviewer[Operation Type]="Valuation")
+
CALCULATE(count(PortCo_Preparer[PortCo Entered By]),ALLSELECTED(PortCo_Preparer))
+
CALCULATE(count(PortCo_Reviewer [PortCo Reviewed By]),ALLSELECTED(PortCo_Reviewer)))

return 
TotalSUM

The following formula "Test" is intended to give me the number of users with "variable" not blank ideally the number will be 3 for the first 3 rows but it is giving me 1s for the first 3 and then a total of 10. 

test = 

var Totaluser = [(CF count - Preparer)]+[(CF count - Reviewer)]+[(Recon count - Preparer)]+[(Recon count - Reviewer)]+[(PortCo count - Preparer)]+[(PortCo count - Reviewer)]

var TotalSUM = 
IF(Totaluser=BLANK(),BLANK(),
CALCULATE(count(CF_NAV_Preparer[Operation Type]),ALLSELECTED(CF_NAV_Preparer),
CF_NAV_Preparer[Operation Type]="Cash Flow"||
CF_NAV_Preparer[Operation Type]="Stock Sale"||
CF_NAV_Preparer[Operation Type]="Stock Distribution")
+
CALCULATE(count(CF_NAV_Preparer[Recon Entered By - 2]),ALLSELECTED(CF_NAV_Preparer),
CF_NAV_Preparer[Operation Type]="Valuation")
+
CALCULATE(count(CF_NAV_Reviewer [Operation Type]),ALLSELECTED(CF_NAV_Reviewer),
CF_NAV_Reviewer[Operation Type]="Cash Flow"||
CF_NAV_Reviewer[Operation Type]="Stock Sale"||
CF_NAV_Reviewer[Operation Type]="Stock Distribution")
+
CALCULATE(count(CF_NAV_Reviewer[Recon Entered By - 2]),ALLSELECTED(CF_NAV_Reviewer),
CF_NAV_Reviewer[Operation Type]="Valuation")
+
CALCULATE(count(PortCo_Preparer[PortCo Entered By]),ALLSELECTED(PortCo_Preparer))
+
CALCULATE(count(PortCo_Reviewer [PortCo Reviewed By]),ALLSELECTED(PortCo_Reviewer)))

var usersss= CALCULATE(IF(TotalSUM>0,COUNTX(Users,[Username])))


var Totalcount2= if(Totaluser>0,TotalSUM,BLANK())
return 
 usersss

So ideally I want to be able to divide 192/3

Thanks for all your help!

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to use the following measure to meet your requirement:

test =
SUMX (
    DISTINCT ( 'Users'[Username] ),
    VAR Totaluser =
        CALCULATE (
            [(CF count - Preparer)] + [(CF count - Reviewer)] + [(Recon count - Preparer)] + [(Recon count - Reviewer)] + [(PortCo count - Preparer)] + [(PortCo count - Reviewer)]
        )
    VAR TotalSUM =
        CALCULATE (
            IF (
                Totaluser
                    = BLANK (),
                BLANK (),
                CALCULATE (
                    COUNT ( CF_NAV_Preparer[Operation Type] ),
                    ALLSELECTED ( CF_NAV_Preparer ),
                    CF_NAV_Preparer[Operation Type] = "Cash Flow"
                        || CF_NAV_Preparer[Operation Type] = "Stock Sale"
                        || CF_NAV_Preparer[Operation Type] = "Stock Distribution"
                )
                    + CALCULATE (
                        COUNT ( CF_NAV_Preparer[Recon Entered By - 2] ),
                        ALLSELECTED ( CF_NAV_Preparer ),
                        CF_NAV_Preparer[Operation Type] = "Valuation"
                    )
                    + CALCULATE (
                        COUNT ( CF_NAV_Reviewer[Operation Type] ),
                        ALLSELECTED ( CF_NAV_Reviewer ),
                        CF_NAV_Reviewer[Operation Type] = "Cash Flow"
                            || CF_NAV_Reviewer[Operation Type] = "Stock Sale"
                            || CF_NAV_Reviewer[Operation Type] = "Stock Distribution"
                    )
                    + CALCULATE (
                        COUNT ( CF_NAV_Reviewer[Recon Entered By - 2] ),
                        ALLSELECTED ( CF_NAV_Reviewer ),
                        CF_NAV_Reviewer[Operation Type] = "Valuation"
                    )
                    + CALCULATE (
                        COUNT ( PortCo_Preparer[PortCo Entered By] ),
                        ALLSELECTED ( PortCo_Preparer )
                    )
                    + CALCULATE (
                        COUNT ( PortCo_Reviewer[PortCo Reviewed By] ),
                        ALLSELECTED ( PortCo_Reviewer )
                    )
            )
        )
    RETURN
        IF (
            TotalSUM > 0,
            1,
            0
        )
)

Best regards,

[amitchandak]

I think you need a subtotal. Please refer

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390