
1. Re: Calculation error
Jim Dehner Aug 2, 2017 5:49 AM (in response to Andrew Morgan)Hi
It is very difficult with the limited data you are sharing but Total() is a function that goes past the viz and to the underlying table  ie. it is looking at the total data set not just the filtered data
if that is not what you intended then try window_sum instead
let me know if that helps
Jim

2. Re: Calculation error
Zhouyi Zhang Aug 2, 2017 6:04 AM (in response to Andrew Morgan)Hi, Andrew
Try replace the ones use Total with similar code below
{Fixed [Your Dimension on the left side]:CountD(if [period] = "Current Month" then [your measure] end)}
/
{Fixed : CountD([your measure])}
ZZ


4. Re: Calculation error
Andrew Morgan Aug 2, 2017 11:25 AM (in response to Jim Dehner)Hi Jim,
You are 100% correct. Total is fetchimg all the records but I only want their grand total value to divide the actual value.
I will try your solution and let you know.
Thanks

5. Re: Calculation error
Andrew Morgan Aug 2, 2017 11:49 PM (in response to Andrew Morgan)Hi Jim,
I tried using the window sum function but it is not giving me the desired result.
Could you please help me?

6. Re: Calculation error
Andrew Morgan Aug 3, 2017 12:29 AM (in response to Andrew Morgan)Hi
,
With customer count, I am getting the correct values. Here the last row is grand total.
Now when I switch to Customer share calculated using the below formula
This is what I am getting:
Attached here the sample workbook created in v10.1.
Please help me.

Dummy.twbx 31.3 KB



8. Re: Calculation error
Andrew Morgan Aug 3, 2017 3:19 AM (in response to Andrew Morgan)Hi,
Here is the updated workbook.

sample.twbx 5.9 MB


9. Re: Calculation error
Andrew Morgan Aug 3, 2017 6:32 AM (in response to Andrew Morgan)Hi,
Did you figure out what is wrong with the calculation?
Thanks in advance

10. Re: Calculation error
Zhouyi Zhang Aug 3, 2017 6:36 AM (in response to Andrew Morgan)1 of 1 people found this helpfulHi, Andrew
Thanks for your workbook, really helpful.
Please see my solution below
I just take current month as example. below calculation code with bold is what I modified
case [Measure]
when "Patient count" then COUNTD(if [Period] = "Current Month" then [Customer ID] END )
WHEN "Rx count" THEN COUNT(IF [Period] = "Current Month" THEN [Customer ID] END )
WHEN "Patient Share" THEN
SUM({ FIXED [Dy Dim]:COUNTD(if [Period] = "Current Month" then [Customer ID] END)})
/
SUM({FIXED:COUNTD(if [Period] = "Current Month" then [Customer ID] END)})
WHEN "Rx Share" THEN
SUM({ FIXED [Dy Dim]:COUNT(IF [Period] = "Current Month" THEN [Customer ID] END)})
/
SUM({FIXED:COUNT(IF [Period] = "Current Month" THEN [Customer ID] END)})
END
Hope this could help and you can apply similar logic to other calculation fields. Please let me know if you still have issue.
ZZ

11. Re: Calculation error
Andrew Morgan Aug 3, 2017 9:57 PM (in response to Zhouyi Zhang)Hi ZZ,
You are great . You nailed it. I can't thank you enough. The only problem is that grand total says 1.01 but it should 1.00. How should I fix this. I want to show values up to two decimal places.

12. Re: Calculation error
Zhouyi Zhang Aug 3, 2017 11:44 PM (in response to Andrew Morgan)1 of 1 people found this helpfulGlad it works for you.
In terms of decimal, the best thing I could think of is use round() function as below.
Hope this could help
ZZ

13. Re: Calculation error
Andrew Morgan Aug 4, 2017 8:08 AM (in response to Zhouyi Zhang)Thanks a ton Zhouyi. You are a Zen master.
cheers,
AM