Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

sum by mainItem

Hi,  What I thought would be an easy tasked proved more complex then I thought   Table A itemSK count a 5 b 5 c 6 d 6 a 7 b 6 b 1 d 3   Item table I...
"DAX"
su
  • OwenAuger's avatar
    OwenAuger
    3 years ago

    Hi Anonymous 

    See the attached PBIX.

     

    First, you should ensure the model is set up like this (which I imagine it already is):

     

    Then create this measure:

    Count by mainitem = 
CALCULATE (
    SUM ( 'Table A'[count] ),
    REMOVEFILTERS ( 'Item' ),
    VALUES ( 'Item'[Mainitemid] )
)

    This measure sums 'Table A'[count] for the values of 'Item'[Mainitemid] corresponding to the visible rows of 'Item', but removing any other filters on the 'Item' table.

     

    Regards

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
3 years ago

Hi Anonymous 

See the attached PBIX.

 

First, you should ensure the model is set up like this (which I imagine it already is):

 

Then create this measure:

Count by mainitem = 
CALCULATE (
    SUM ( 'Table A'[count] ),
    REMOVEFILTERS ( 'Item' ),
    VALUES ( 'Item'[Mainitemid] )
)

This measure sums 'Table A'[count] for the values of 'Item'[Mainitemid] corresponding to the visible rows of 'Item', but removing any other filters on the 'Item' table.

 

Regards