Help power pivot to exclude a value

%3CLINGO-SUB%20id%3D%22lingo-sub-3301752%22%20slang%3D%22en-US%22%3EHelp%20power%20pivot%20to%20exclude%20a%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301752%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20use%20power%20pivot%20and%20I%20created%20a%20measure%20for%20total%20sales%20To%20calculate%20the%20sales%20on%20each%20seller%20I%20use%20this%20measure%20with%20reference%20to%20a%20cube%20member%20and%20can't%20find%20problems.%20I%20wonder%20if%20it%20is%20possible%20use%20member.cubo%20for%20find%20sales%20for%20all%20sellers%2C%20except%20the%20seller%2C%20for%20example%2C%20tony%3F%20I%20hope%20I%20was%20clear%20thanks%20to%20those%20who%20will%20have%20the%20patience%20to%20help%20me%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3301752%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302363%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20power%20pivot%20to%20exclude%20a%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302363%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ethanks%20for%20your%20help%20if%20I%20have%20not%20misunderstood%2C%20there%20is%20the%20possibility%20to%20simply%20write%20a%20cube%20member%20with%20negative%20value%20-tony%20to%20have%20the%20same%20result%3F%20I%20misunderstood%20%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3301833%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20power%20pivot%20to%20exclude%20a%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301833%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1380097%22%20target%3D%22_blank%22%3E%40Pissi2019%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%20model%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20500px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F368984i3F260FDE6511D419%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewe%20may%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DCUBEVALUE(%0A%20%20%20%20%20%20%22ThisWorkbookDataModel%22%2C%0A%20%20%20%20%20%20%22%5BMeasures%5D.%5BSales%5D%22%2C%0A%20%20%20%20%20%20%20CUBESET(%22ThisWorkbookDataModel%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22EXCEPT(%0A%20%20%20%20%20%20%20%20%20%20%20%20%5BtblSales%5D.%5BSeller%5D.%5BAll%5D.children%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%5BtblSales%5D.%5BSeller%5D.%5BTony%5D%20)%22%0A%20%20%20%20%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3326796%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20power%20pivot%20to%20exclude%20a%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3326796%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1380097%22%20target%3D%22_blank%22%3E%40Pissi2019%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20not%20sure%2C%20didn't%20use%20such%20tuple.%20Here%20logic%20is%20straightforward%20-%20you%20get%20measure%20value%20from%20the%20set%20of%20all%20members%20except%20something.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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.