• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Use Changed Pivot Values in Calculated Fields

W

Willstein818

New Member
Hi, I'm new to this channel, been working with excel and just recently tap into slicers and dashboards. I have a question:

So, I want to create a calculated field in pivot table. But, the field's value that I want to use is actually the changed one (Modified by using "Shown Value as" Option, I modified the value to "Show as % of Grand Total" format)). When I enter the formula, it still use the original value, not the changed one. Is there any way I could use the changed value in the calculated fields? Thanks for the help.
 
Dear Mr.Rahulshewale1,

Below are my files,

On this file, at the rightmost pivot table (PivotTable9), I want to set the target per stores based on Sum of Base Modified numbers * specified target values (For the sample, I set the total target to 1800), and the result will be shown at "Sum of Target" values. However, as you can see, the sum or target didn't reach 1800 in total, which implies that the formula is not working. Could you give me some insights about how to solve this problem? Thanks a lot & Merry Christmas!.

7269972700
 

Attachments

  • Sample.xlsx
    637.9 KB · Views: 1
Hi @Willstein818,

See if is it ok ?

Power Pivot Solution.


Willstein818 said:
Dear Mr.Rahulshewale1,

Below are my files,

On this file, at the rightmost pivot table (PivotTable9), I want to set the target per stores based on Sum of Base Modified numbers * specified target values (For the sample, I set the total target to 1800), and the result will be shown at "Sum of Target" values. However, as you can see, the sum or target didn't reach 1800 in total, which implies that the formula is not working. Could you give me some insights about how to solve this problem? Thanks a lot & Merry Christmas!.

View attachment 72699View attachment 72700
Click to expand...
 

Attachments

  • PT.xlsx
    689 KB · Views: 1
Dear Mr. Rahul Shewale,

Thanks a lot! Definitely will learn more about Power Pivot and DAX. In the meantime, I notice that you also used regular tables in setting up the target value. Is it possible to have a conditional target value for each ItemName? And in the ItemName slicers, some of the items are unnecessary but I can't seem to find a way to hide / delete it, is it also possible to do that? Since the dashboard is for users and the slicers could potentially cause confusion. Thank you very much for your help.

rahulshewale1 said:
Hi @Willstein818,

See if is it ok ?

Power Pivot Solution.
Click to expand...

Regards,
William
 
You must log in or register to reply here.
Back
Top