How do I sum if or count if using conditional values?
Hi Community,
I'm trying to figure out why my pivot table is returning incorrect values, and how to fix it.
I need a variety of formulas that apply to each program in my pivot table to collect data about how much money we are contracted to receive, how much money we have collected to date of the report exported, how many students have only paid their deposit and what amount that totals, as well as how many students are considered Confirmed, and how many are on Scholarship. Here's a snapshot of what this long pivot table looks like:
Committed Revenue should be =sum(total orders) but it's inflating the value from what is in the data. When I click into the formula it looks like this, which is why I think it's inflating, but I can't figure out how to get it to read the data correctly.
Total Paid to date is having a similar issue. It should be =sum(Total Payments) and it looks like this in the formula field, containing all items instead of sum:
Deposit Paid Only (Dollar Amount) is a bit more interesting but I still can't get it to look right. It should be =sum(Total Payments) when total payment = 50. I have the filter set to =50 but it is not returning the correct value.
Deposit Paid Only (Number of Users) is similar to the deposit paid only dollar amount, and should =count(Applicant ID) when Total Payments = 50. When I add Total Payments to this formula, the filter on Total Payments goes away, so I can't get it to only count when Total Payments = 50.
I have a couple of fields in this pivot table that are calculating mostly correctly, are able to use filters, and they are set up like this:
to count the number of students (applicant IDs) in the program when confirmation status = confirmed
to count the number of students (applicant IDs) in the program when confirmation status = confirmed and payment result = scholarship
I need a few basic formulas that I can then change to fit the needs of each field, but everything I've read online isn't making sense. I know I may need a CASE statement but I've tried that a couple times and still no luck.
Please help!
Hi oliviar
A sum of a total (SUM(TOTAL)) should not mess up your sum (atleast not in my sisense deployment). If you think the numbers are to high, it could indicate that there is a many to many happening. This extrapolates the data. Enable/download the visualise jaql from the marketplace to determine if this is the case. if so you can change it and the numbers will be correct.
Second; you cannot filter on a sum or on a value that is being summed / aggregated. To do a filtered measure (or sum in your case) try the following (SUM(TOTAL_ORDER), [TOTAL ORDER]) . Filter on that last field and select 'All items' when selecting the field. Then the filter will work
Hope this helps
