
1. Re: Data variance data based on different measures
Paul Field Apr 17, 2018 9:20 PM (in response to bala kumar)Hi Bala,
The easiest way to do this is to treat each measure separately (in a separate worksheet)  that way we can add the a calculated field based on the measure e.g for your rates calc I have created a calulated field "rate direction":
IF ZN(SUM([Rate]))  LOOKUP(ZN(SUM([Rate])), 1) > 0 THEN "UP"
ELSEIF ZN(SUM([Rate]))  LOOKUP(ZN(SUM([Rate])), 1) < 0 THEN "Down"
ELSE "Steady"
End
We can then add to the shapes shelf (and to the color shelf) and then choose the appropriate shape (and color). Then the worksheets can be brought together in a dashboard.
In the attached I have done this for rates, but the same can be applied to your other measures.

Bala_Test PF.twbx 60.7 KB


2. Re: Data variance data based on different measures
bala kumar Apr 17, 2018 10:49 PM (in response to Paul Field)Hi Paul,
Thanks for the reply.
In my case, How can i get the difference of the values as when I do the table calculation it doesn't provide me the same output format.
Even If I calculate the difference in a separate sheet and i am not able to align the same in dashboard.
Could you help on getting the difference & diff %
Thanks,
Bala

3. Re: Data variance data based on different measures
Paul Field Apr 17, 2018 11:07 PM (in response to bala kumar)Sure...here's the step by step process.
Step 1: Simplify worksheet to a single measure:
Step 2: Change measure to a difference (and/or % difference)
Step 3: Drag rate back onto text shelf
Step 4: Double click on the Table calc and copy the formula which is: ZN(SUM([Rate]))  LOOKUP(ZN(SUM([Rate])), 1)
Step 5: Create a new calculated field “Rate direction” which uses this formula:
IF ZN(SUM([Rate]))  LOOKUP(ZN(SUM([Rate])), 1) > 0 THEN "UP"
ELSEIF ZN(SUM([Rate]))  LOOKUP(ZN(SUM([Rate])), 1) < 0 THEN "Down"
ELSE "Steady"
End
Step 6: Drag “Rate Direction” onto the shape shelf. Ensure the table calc is set to compute as “table across” and select the appropriate shape for up/down/steady.
Step 7: Drag “Rate direction” onto colour shelf and select colours for up/down/steady (choose white for steady so that it is not displayed against white background)
Step 8 (optionally) remove the Table calc from the text shelf so the difference value isn’t displayed.
Then if you create 3 sheets, one for each measure, you should be able to align in your dashboard no problem  we know they will all be 1 row and 2 columns, so shouldn't be an issue  unless I'm misunderstanding something?

4. Re: Data variance data based on different measures
bala kumar Apr 18, 2018 12:09 AM (in response to Paul Field)Thanks a lot Paul. Highly appreciated.
Will try the solution and update you on it.
Thanks,
Bala

5. Re: Data variance data based on different measures
bala kumar Apr 18, 2018 12:32 AM (in response to Paul Field)Paul, In this case i need the difference value and the up/down symbol as a third column as like how we get in excel cell by cell difference.
Regards,
Bala

6. Re: Data variance data based on different measures
Paul Field Apr 18, 2018 12:55 AM (in response to bala kumar)We can do that again by splitting into different sheets. See attached. However, I would generally recommend on going the path of trying to replicate an excel file. It's often possible, but can add complexity and difficulty and there may just be a different way of looking at the data which is much simpler (and performs better) and gives the same answer, just in a slightly different way.

Bala_Test PF.twbx 81.5 KB


7. Re: Data variance data based on different measures
bala kumar May 2, 2018 6:10 PM (in response to Paul Field)Thanks Paul. It all worked well.
Apologies for the late reply.
Regards,
Bala