Using data created in PowerBi pivot in new calculated values table

[mespinosa]

I have created a pivot table in PowerBi. How can I create a new table that uses the data from the pivot table in calculated values? I also need it to be dynamically changing with slicers so that both the pivot table and the resulting table using the pivot table data in calculated values change together. In excel it would be a matter of creating a new sheet and referencing the cells in the pivot table to calculate values. How would I go about doing this in PowerBi? Is there a way to get the pivot table into the fields section?

[miespino]

I forgot to mention, I would also like to be able to slice the pivot table so that it dynamically changes the calculated table as well when sliced. For example lets say I would like to slice by name as shown below:

The values in the calculated table should also change because it is now referencing the new sliced pivot table values. So it would be showing the Cumulative vol percent for code A and B for each score for Amy only. 

[miespino]

Hi @Anonymous 

Thanks for your response. I am still a bit confused. I have attached photos for reference. Say I have the data shown below:

I then create a pivot table from this data, as shown below: 

Now lets say I want to use the values in this pivot table to generate new calculated values. For example, lets say I would like calculate the Cumulative vol percent for code A and B at score 80. So in this case I would want to look at the pivot table and take the sum of cells B2:B3 and D2:D3 and divide by the sum of cells B1:B3 and D1:D3.

And lets say I would like to repeat that process for the rest of the scores to create a new table that holds the Cumulative vol percent for code A and B for each score.

Essentially, I need to be able to reference the values in the pivot table to create a new table with calculated values. How can I do this in powerbi? If I were to do this in excel, the pivot table would be on another sheet called "Pivot" and the new table's calculated value would just be 

=(SUM(Pivot!B2:B$3) + SUM(Pivot!D2:D$3))/(SUM(Pivot!B$1:B$3) + SUM(Pivot!D$1:D$3))

Any clarification on this would be much appreciated!

Thank you

[Anonymous]

Hi @mespinosa ,

Has your problem been solved? If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out. Thanks in advance.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[GilbertQ]

Hi @mespinosa 

It sounds to me like you need to use the new Field Parameters which you can find more details below:

Fields parameters in Power BI - SQLBI

[miespino]

Hi, I think the fields parameter will help with dynamic slicing but how can we get data resulting from a pivot table into the fields section to use for new calculatied measures or visualizations? For example if we wanted to create a table based on the data in the generated pivot table.

[Anonymous]

Hi @mespinosa ,

You can try table functions such as summarize(), addcolumns(), values().
If you mean transpose tables, then you can use the union() function.

Please refer to the following document for more information.

Table manipulation functions (DAX) - DAX | Microsoft Learn

Transpose a table - Microsoft Power BI Community

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.