Help power pivot to exclude a value

Copper Contributor

I use power pivot and I created a measure for total sales To calculate the sales on each seller I use this measure with reference to a cube member and can't find problems. I wonder if it is possible use member.cubo for find sales for all sellers, except the seller, for example, tony? I hope I was clear thanks to those who will have the patience to help me

3 Replies

@Pissi2019 

For such sample model

image.png

we may use

=CUBEVALUE(
      "ThisWorkbookDataModel",
      "[Measures].[Sales]",
       CUBESET("ThisWorkbookDataModel",
            "EXCEPT(
            [tblSales].[Seller].[All].children,
            [tblSales].[Seller].[Tony] )"
      )
)

@Sergei Baklan 

 

thanks for your help if I have not misunderstood, there is the possibility to simply write a cube member with negative value -tony to have the same result? I misunderstood ?

@Pissi2019 

I'm not sure, didn't use such tuple. Here logic is straightforward - you get measure value from the set of all members except something.