How do I use DAX or Sets/MDX to create the equivalent of a calculated item

[BruceP]

I have large tables of sales data with products that are split into various groups.

Some managers like sales split into individual groups, for example with the rows showing sales for:

Flips
Flops
Blips
Blobs

Some managers like the rows combined:

Flips & Flops
Blips & Blobs

Prior to PowerPivot, I just created Calculated Items to achieve this (simply =Flips+Flops and =Blips+Blobs)

I have searched high & low to find a solution and have drawn a blank so far. How do I achieve this with PowerPivot?

Is it done with DAX or should it be done by creating a custom set (with some MDX expressions)?

Either way, I would appeciate some hand-holding through this. Any help would be very much appreciated.

 

[Guenh]

Hi Bruce, Have you found maybe a solution to your problem? I'm trying to do similar thing now, but with no luck...

 

[BruceP]

Hi Guenh,

I have found a solution but it didn't involve using DAX.

Taking the categories I used as an example above, I created a new table with four entries in it. In the first column would be Flips, Flops, Blips and finally Blobs on seperate rows. In the second column I put "Flips & Flops" against both Flips and Flops and "Blips & Blobs" against Blips and Blobs.

I then added this table to PowerPivot and then you can drag the "Flips & Flops" and "Blips & Blogs" fields into a PowerPivot table and PowerPivot crunchs the number for you.

Though perhaps not the neatest solution I've used it a number of times when this 'problem' arises. There may well be a DAX solution but I've not found it yet. If you do, please let me know.

Hope that helps.