• 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.

CUBE Formula approach in Slicer

senthil murugn

New Member
Our Project is having multiple divisions like mechanical, Schematics, SW Developer, testing etc..,

Each division is assigned with some N no of Tasks.

Task status is declared as follows
====================================
STATUS DevStatus VerifStatus
====== ========= ===========
100% Completed = Devloper status = 100% & Cross Verification person's status = 100%
Pending = Devloper status = 100% & Cross Verification person's status != 100%
In Progress = Devloper status < 100% & Cross Verification person's status != 100%
Not Yet Opened = Developer status = 0

Created a Pivot chart & Slicer in Sheet6,
Slices are Division, DevStatus, VerifStatus & Task ID
and based on selection(all slicer) Task ID slicer is displaying respective Task Id's perfectly.


Our requirement is count the no of TASK ID from Slicer_Task_ID under each cases with some formula like
=Function(find the number of Tasks actively selected)

With your website reference used
=CUBESETCOUNT(Slicer_Task_ID) but its returning N/A

IT would be thankful if you could help us to resolve the issue.
Please find the attached sheets.

Used Excel2019.

Thanks
Senthil M
 

Attachments

  • Book4.xlsx
    45.4 KB · Views: 8
The CUBE functions need the data to be in the Data Model.
On sheet Sheet6b of the attached, I've created a new Pivot the same as on sheet Sheet6 but this time with the Add this data to the Data Model checkbox ticked. Added a slicer.
Now see formula in cell B9.
Unfortunately, the count is 1 when all the items are selected!
 

Attachments

  • Chandoo47903Book4.xlsx
    250.5 KB · Views: 15
Thank you so much, Once we know all tasks are completed then no need to check the no of slicer item.
It needed only when project is in progress condition.

Anyway thank you so much, through your website only I learned lot about excel use cases.
 
Back
Top