Forum Discussion

mespinosa's avatar
mespinosa
New Member
3 years ago

Using data created in PowerBi pivot in new calculated values table

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?
Dataflow
Need Help
Tutorial Requests

6 Replies

    • miespino's avatar
      miespino
      Frequent Visitor
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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. 

  • miespino's avatar
    miespino
    Frequent Visitor
    3 years ago

    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

     

  • miespino's avatar
    miespino
    Frequent Visitor
    3 years ago

    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.