Add pivot table column without Exel creating a hierarchy

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

Add pivot table column without Exel creating a hierarchy

Postby IanR » Wed Mar 28, 2018 11:17 pm

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

noorbaksh
Posts: 3
Joined: Tue Mar 27, 2018 5:57 pm

Re: Add pivot table column without Exel creating a hierarchy

Postby noorbaksh » Sat Mar 31, 2018 2:55 pm

submit raw data along with result that would you like


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests

cron