Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Again,
I have been trying this all morning, and figured I would put it out there as there has got to be an easy answer that clearly won't just come to me 🙂
Essentially I am trying to build a table that will only show the total number of programs is with a certain assessment that is done . The end goal is to have a 'heat-map' like display highlighting groups that have a low-level of completion.
I am looking at the current stage of a program and including the program if the assessment for that stage is done. I can get it to display properly if I display the stage in the table, but once I remove the dimension from my table, the calculation isn't working.
I also tried to just hide the dimension but the list keeps the rows for that dimension and I want it to be a simple (uncollapsed) view similar to this:
Group | List Done Count |
5 | |
a | 1 |
b | 3 |
c | 1 |
As a reference, here is my complete table for the example above:
Group | Program # | Stage | Go-To Seed List Done | Go-To-Alpha List Done | Go-To-Beta List Done | Count (Program) | List Done Count |
9 | 0 | ||||||
a | 2 | Alpha | Yes | No | Yes | 1 | 0 |
a | 1 | Seed | Yes | Yes | Yes | 1 | 1 |
a | 3 | Seed | No | Yes | Yes | 1 | 0 |
b | 4 | Beta | Yes | No | Yes | 1 | 1 |
b | 6 | Alpha | Yes | Yes | Yes | 1 | 1 |
b | 5 | Seed | Yes | Yes | Yes | 1 | 1 |
c | 7 | Beta | Yes | No | No | 1 | 0 |
c | 8 | Beta | Yes | No | No | 1 | 0 |
c | 9 | Alpha | Yes | Yes | No | 1 | 1 |
Here is the formula I am using for 'List Done Counts':
(If([Stage]='Seed',Count({<[Go-To Seed List Done] = {'Yes'}>}[Program]),
If([Stage]='Alpha',Count({<[Go-To-Alpha List Done] = {'Yes'}>}[Program]),
If([Stage]='Beta',Count({<[Go-To-Beta List Done] = {'Yes'}>}[Program]),0))))
Attached is the dashboard in case that is easier.
Thanks in advance for any guidance!
Try using this
RangeSum( Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}>}[Program]), Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}>}[Program]), Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}>}[Program]) )
Try using this
RangeSum( Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}>}[Program]), Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}>}[Program]), Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}>}[Program]) )
Of course it works just as I was hoping for!
Thank you!
Just curious, is there an easy explanation as to why RangeSum doesn't need for the the dimension to be in the table versus the IF statements needing it to be?
You don't really need the RangeSum() here... this would work the same way
Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}>}[Program]) + Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}>}[Program]) + Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}>}[Program])
But I like to use RangeSum() because it can handle null better than the plus (+) sign. For example RangeSum(10 + Null()) = 10, but 10 + Null() = Null().
The change I made is to convert If statement to set analysis... if statement didn't work because there were multiple stages for each group. When you said Stage = ...., if worked when stage was a dimension, but when stage is not a dimension.... the chart has multiple stages and nulls out because it doesn't know what exactly to use. To fix this, you can use Aggr() function with Group and Stage as dimension
Sum(Aggr( (If([Stage]='Seed',Count({<[Go-To Seed List Done] = {'Yes'}>}[Program]), If([Stage]='Alpha',Count({<[Go-To-Alpha List Done] = {'Yes'}>}[Program]), If([Stage]='Beta',Count({<[Go-To-Beta List Done] = {'Yes'}>}[Program]),0)))), Group, Stage))
But why complicate things, when we can simplify them
Agreed! and that makes total sense.
Thanks again for your help!
Cheers!