Forum Discussion

Mavrick786's avatar
Mavrick786
New Member
2 years ago
Solved

Calculate sum for common items under multiple selected categories & slicers also show same behaviour

Hi, I have a table with inventory data for toys with certain grades. Below you will find a small sample of the same. There are to be two slicers on the page. Toys and Grade. For any two or more...
Need Help
  • danextian's avatar
    danextian
    2 years ago

    Hi Mavrick786 ,

    Try these measures

     

     

     

    Toy Count = 
VAR SelectedToys=
    ALLSELECTED ( 'Table'[Toys] )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( 'Table'[Toys] ) ),
        REMOVEFILTERS (),
        VALUES ( 'Table'[Grade] ),
        SelectedToys
    )

    Inventory Sum = 
VAR __DISTINCT_TOY_COUNT =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Toys] ), ALLSELECTED ( 'Table' ) )
VAR __GRADE_TABLE =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( 'Table', 'Table'[Grade] ),
            "@Toy Count", [Toy Count]
        ),
        [@Toy Count] = __DISTINCT_TOY_COUNT
    )
VAR __GRADE_COLUMN =
    SELECTCOLUMNS ( __GRADE_TABLE, "@Grade", [Grade] )
RETURN
    CALCULATE ( SUM ( 'Table'[Inventory] ), 'Table'[Grade] IN __GRADE_COLUMN )

     

     

    Please see attached pbix for details