Fixed Beast Mode for Percent of Category

lb1234

We're trying to get a percent of the total for each series category on this chart by using a fixed function beast mode:

We're displaying the number of unqualified leads on the y axis and using the fixed beast mode as a tooltip field used in the data label settings. On the 27th the % should be around 23% as the total leads created that day were 642 and the number of the leads disqualified is 149. What am I doing wrong here?

ArborRose

I think you are trying to do too much in one column. I would break those out into separate columns so you can see the information in a table display across all companies. Then filter or group by.

Lead_Count:
CASE WHEN Status='Unqualified' THEN COUNT(Lead ID) END

Lead_Total:
CASE WHEN Status='Unqualified' THEN SUM({amount}) END

Then you find rate by dividing whatever….such as Lead_Count/Lead_Total.

MichelleH

@lb1234 Do you have Status as your series in the chart? If you do, you could just use the Percent of Category macro in the Data Label Settings.

Note that if your two bars are made up by separate beast modes this will not work the same way

lb1234

This wouldn't work for my use case but I did find a solution by wrapping the denominator with the fixed by function and adding the date being used as the filter on the card to the fixed by statement:

COUNT(DISTINCT CASE WHEN Status = 'Unqualified' THEN Lead ID END) / (SUM(COUNT(DISTINCT Lead ID) FIXED(BY Company, Lead.CreatedDate)))