
1. Re: Can we create Greater/Less than symbol between values comparing one another?
Ken Flerlage Jul 18, 2019 1:23 PM (in response to Ramesh Pitchaikani)Does your data only have 4 generations or is it possible that there is a much larger number of options?

2. Re: Can we create Greater/Less than symbol between values comparing one another?
Ramesh Pitchaikani Jul 19, 2019 1:52 AM (in response to Ken Flerlage)Hi Ken,
My dataset has only 4 generations, I wouldn't expect that to be changed often. Hence you can assume as static value not going to change forever.
I have published my workbook in the public which you can use it for try. It is available here. Thanks!

3. Re: Can we create Greater/Less than symbol between values comparing one another?
Ken Flerlage Jul 19, 2019 8:52 AM (in response to Ramesh Pitchaikani)OK, so this proved to be pretty complicated. I may be overcomplicating it, so I'll welcome other responses. To do this, we need to rank the values within each category, but I couldn't find a good solution using Rank because that solution always required me to compute the table calculation using Generation, which then wouldn't allow me to create a single value for the text. So, I did some stuff with FIXED LODs instead.
First, I created calculated fields to get the first highest, second highest, third highest, and fourth highest values within each category:
Category Max Value 1
// Get the first highest value for the category
{FIXED [Category]: MAX([% of Spending])}
Category Max Value 2
// Get the second highest value for the category
{FIXED [Category]: MAX(
IF {FIXED [Category], [Generation]: SUM([% of Spending])} < [Category Max Value 1] THEN
{FIXED [Category], [Generation]: SUM([% of Spending])}
ELSE
NULL
END
)}
Category Max Value 3
// Get the second highest value for the category
{FIXED [Category]: MAX(
IF {FIXED [Category], [Generation]: SUM([% of Spending])} < [Category Max Value 2] THEN
{FIXED [Category], [Generation]: SUM([% of Spending])}
ELSE
NULL
END
)}
Category Max Value 4
// Get the second highest value for the category
{FIXED [Category]: MAX(
IF {FIXED [Category], [Generation]: SUM([% of Spending])} < [Category Max Value 3] THEN
{FIXED [Category], [Generation]: SUM([% of Spending])}
ELSE
NULL
END
)}
Then, I compared these values to each generation's values to get the generations ranked 1, 2, 3, and 4:
Rank 1
// Generation with the highest value for the given category.
IF {FIXED [Category], [Generation]: SUM([% of Spending])} = [Category Max Value 1] THEN
[Generation]
END
Rank 2
// Generation with the second highest value for the given category.
IF {FIXED [Category], [Generation]: SUM([% of Spending])} = [Category Max Value 2] THEN
[Generation]
END
Rank 3
// Generation with the third highest value for the given category.
IF {FIXED [Category], [Generation]: SUM([% of Spending])} = [Category Max Value 3] THEN
[Generation]
END
Rank 4
// Generation with the fourth highest value for the given category.
IF {FIXED [Category], [Generation]: SUM([% of Spending])} = [Category Max Value 4] THEN
[Generation]
END
Finally, I use an LOD to collapse these four fields into a single line of text for each category:
Spending Text
// Generated text showing which generation spent more than another for a given category.
{FIXED [Category]: MAX([Rank 1])} + " > " + {FIXED [Category]: MAX([Rank 2])} + " > " + {FIXED [Category]: MAX([Rank 3])} + " > " + {FIXED [Category]: MAX([Rank 4])}
One problem with this solution is that ties could create some poor results. I don't have a solution for that, unfortunately.