Skip to main content

Hi Pigment Community,

 

I am trying to create a KPI that tracks the ratio of blank cells to filled cells for a column in a transaction list. In addition I want this KPI to be dimensioned so that it can be affected by the page filters, however I can’t seem to figure out a solution and was wondering if any of you had a solution for me. 

So far I have attempted 4 different solutions, however none of them have given me exactly what I am looking for. 

Below I have provided screenshots of the different parts that contribute to my problem. As I have tried different solutions and none of them have worked I have provided information for each formula, as I don’t understand why each one gives me a different output. My end goal is to have the proper ratio of (all blanks) / (all blank + nonblank) which can be filtered by dimensions using the page option. Specifically: year, deal status, and HB - Deal Owner. 

Extra info:

All deals - total row count = 5,504

All deals.segment = blank = 292

Ratio = 292 / 5504 * 100 = 5% (rounded)

 

All Deals - Transaction Block (where the segment column lives)
Segment - Dimensioned by Segment Simplified
Dashboard with the results of each attempted formula

Formula’s for KPIs

Segment - % missing data

This formula is correct, however the dimensions are removed. Thus, I can’t filter on dimensions, so my kpi is static. Also, when I try to add them back it doesn’t seem to work. Perhaps I don’t understand how the dimensions can be readded?

COUNTBLANKOF('All Deals'.Segment)/COUNTALLOF('All Deals'.Segment)

Segment - % missing data 2

In this formula I tried to take the count of my segment and apply a filter to then divide it by my count when I don’t have a filter applied. From my understanding this should find all my blank values and then divide by all my total values. However, the outcome is not what I expected.

NOTE: ‘segment filled’ is an additional column I made so that I could dimension blank cells in segment as ‘missing data’. 

('All Deals'.'Segment Filled'(filter: 'All Deals'.'Segment Filled'.'Segment Simplified' = 'Segment Simplified'."Missing Data"]tby count: 'All Deals'.'Create Month', 'All Deals'.'Deal owner', 'All Deals'.'Deal Status'])/('All Deals'.'Segment Filled'(by count: 'All Deals'.'Create Month', 'All Deals'.'Deal owner', 'All Deals'.'Deal Status'])

Segment - % missing data 3

In this formula I tried to take the count of blanks for segment dimensioned by create month, deal owner, and deal status and then divide by the total count. Again this did not work as I expected. 

'All Deals'.Segment

Segment - % missing data 4

My last attempt. Here I created two metrics and then tried to divide them by one another. My hope was that I could have the dimension in the metric, and when I divided them by one another the dimensions would stay and then I could put them in the page selector to filter by on the view. Unfortuantely, this did not work either. This did bring me the closest to my solution though, however by looking at the metric results you can see that the calculated values are smaller than what is present in the ‘All Deals’ transaction list. 

'All Deals - Missing Deal Stage (blanks)'/'All Deals - Missing Deal Stage (All)'
Notice the value is lower than actual, 24 vs 292
Notice the value is lower than actual, 2330 vs 5504

 

 

I would really appreciate any help and understanding you could to provide to me. Thank you in advance, and apologies for the long post.

 

Thank you!

Hi @darious ,

Ratios could be a tricky especially if you are looking to have it across all summary levels.

In your case I would actually apply the last option by creating 2 metrics: 
1. Counting the blanks
2. Counting all records
However, since the two metrics are not calculating correctly I believe it would be cause by having blanks in the properties you aggregating upon (either the month, owner or deal status)
So as a first step I would verify I have no blanks or fill the properties you are using for the aggregations.
Once this is sorted out, the two metrics should output the correct result.

In order to have the correct ratio across all levels, it would be best to leverage the calculated items feature.
This would be possible by following these steps:

Step 1: Ensure the numerator and denominator of the ratio you are calculating are in two separate metrics

Step 2: Add both metrics to a table
Step 3: Click 'add this metric again' on the numerator metric. Rename the metric to the name of your ratio
Step 4: Right click on the ratio, press 'show value as', % of, another metric, choose denominator

Step 5: Change formatting to be a number instead of a %

The steps are more detailed in the article How to calculate ratios and averages at summary levels.

Finally, you would use this table for the KPI metric by hiding the other metrics and keeping only the ratio.
 


Hope this helps,
Issam

Reply


Ask your question here!

Terms & ConditionsCookie settings