Add Ppivot table column without excel creating a hierarchy

Anything related to PowerPivot and DAX Formuale
IanR
Posts: 13
Joined: Wed Aug 09, 2017 11:30 pm

Add Ppivot table column without excel creating a hierarchy

Postby IanR » Thu Mar 29, 2018 4:04 am

Hi all,

Is it possible to add a (Power) pivot table column without Excel creating a hierarchy on the fly? I am creating several pivots which will compare data from two sources in the model, a sales table and a repairs table. In all the pivot tables I would like the first column to be total sales qty (in the filtered period) from the sales table. I would like the subsequent columns to be based on a column from the repairs table (for example, one will be months from sale to first repair). I have product group on rows, which comes from a lookup table that is linked to both tables.

When I try to add the qty and (in the example) 'months to first repair' fields to the columns area of the pivot a sort of hierarchy is created with the same total sales figure repeated for each months to first repair column.

I could do this using CUBEVALUE expressions but I do not know in advance how many rows or columns there are going to be. I don't know if there is a way to make the number of CUBEVALE cells dynamic and vary according to the number of rows and columns in the data.

Thanks
Ian

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 8 guests