Summing items from categories that fulfil a criteria

[yf_f]

Hi, I would like to compute the total number of units for categories that have more than 2 distinct users, and display the total as a measure using DAX.

First, I have to figure out which categories have more than 2 distinct users.
Then, I would like to sum all the units for these categories.

This "fact_units" table is an example of the input fact table.

Category	User	Units
Cat A	1001	3
Cat A	1002	4
Cat A	1003	1
Cat B	1001	4
Cat B	1001	4
Cat C	1003	2
Cat D	1004	3
Cat D	1005	1
Cat D	1006	6
Cat D	1007	3

As an intermediate step, the table illustrated below removes category B and C because they have 1 distinct user each.

Category	Distinct users	Unit subtotal
Cat A	3	8
Cat D	4	14

Finally, the measure should return 22.
 
How can I go about doing this? Thanks in advance 🙂

[johnt75 · Accepted solution]

Try

Units multiple users =
VAR CategoriesAndUsers =
    ADDCOLUMNS (
        VALUES ( 'Fact Units'[Category] ),
        "@num users", CALCULATE ( DISTINCTCOUNT ( 'Fact Units'[User] ) )
    )
VAR FilteredCategories =
    FILTER ( CategoriesAndUsers, [@num users] > 1 )
VAR Result =
    CALCULATE ( SUM ( 'Fact Units'[Units] ), FilteredCategories )
RETURN
    Result

[Bibiano_Geraldo]

Hi @yf_f ,

You can achieve your goal by this DAX measure:

Units for Categories with More Than 2 Users = 
SUMX (
    FILTER (
        ALL ('Fact_Units'[Category]), 
        CALCULATE ( DISTINCTCOUNT ('Fact_Units'[User]) ) > 2
    ),
    CALCULATE ( SUM ('Fact_Units'[Units]) )
)

it will return 21 as shown bellow:

[Ashish_Mathur · Accepted solution]

Hi,

The answer should be 21.

PBI file attached.

Hope this helps.

[FreemanZ]

hi @yf_f ,

you may also try like:

measure =

SUMX(

FILTER(

ADDCOLUMNS(

VALUES(fact[Category]),

"UserCount",

CALCULATE(DISTINCTCOUNT(fact[User])),

"UnitsTtl",

CALCULATE(SUM(fact[Units]))

),

[UserCount]>1

),

[UnitsTtl]

)

[johnt75 · Accepted solution]

Try

Units multiple users =
VAR CategoriesAndUsers =
    ADDCOLUMNS (
        VALUES ( 'Fact Units'[Category] ),
        "@num users", CALCULATE ( DISTINCTCOUNT ( 'Fact Units'[User] ) )
    )
VAR FilteredCategories =
    FILTER ( CategoriesAndUsers, [@num users] > 1 )
VAR Result =
    CALCULATE ( SUM ( 'Fact Units'[Units] ), FilteredCategories )
RETURN
    Result