Forum Discussion
How to fix a Non-addictive calculation?
Hi there,
I have a matrix showing the wrong results on the total (not surprising, right?), but the rows are correct. I exported the data to Excel to double-check.
There is no filtering on the page but in the matrix instead of summing the rows individually, it actually uses the results of each column to return the total.
I already tried FILTERS and ALL/ALLEXPECT parameters but could not figure it out.
This is the formula of Column B, which does a distinct count of all days with sales by location in the current month excluding Sundays.
Selling Days =
CALCULATE (
AVERAGEX (
SalesReport,
DISTINCTCOUNT ( SalesReport[Date Sold] )
),
WEEKDAY (
SalesReport[Date Sold],
2
) <> 1
)
//Excluding Sundays
This is the formula of Column C, which returns the expected days of sales for the current month.
Expected Selling Days = CALCULATE (
AVERAGE ( 'Sales Projection 2023'[knocking_days] ),
'Sales Projection 2023'[knocking_days]
<> BLANK (),
'Sales Projection 2023'[knocking_days] <> 0
)
//Excluding Zeros and/or blanks
This is the formula of Columb D, which sums the sales excluding cancellations:
Actual($) = CALCULATE (
SUMX (
SalesReport,
SalesReport[Initial Service Price]
),
SalesReport[Status]
IN {
"Pending",
"Serviced",
"Not Serviced"
}
)
This is the formula of Columb E, which divides the result of the actual sales by selling days (column B) and multiplies by expected selling days (column C), to calculate the projected Sales for the Month end.
Expected ($) =
VAR A = [zAct. IR]
VAR B = DIVIDE ( [zProjected Service Days], [zEstimated IR2] )
RETURN
A*B
3 Replies
raphazzz , Multiplication needs to be done at the row level or virtual table level. Assume you have a location dimension. Change calc in one of the way
Expected ($) =
VAR A = [zAct. IR]
VAR B = DIVIDE ( [zProjected Service Days], [zEstimated IR2] )
RETURN
Sumx(values(Location[Location]), calculate( A*B
))
Or
Expected ($) =VAR A = [zAct. IR]
VAR B = DIVIDE ( [zProjected Service Days], [zEstimated IR2] )
RETURN
Sumx(values(Location[Location]), ( A*B
))It still returning the wrong total. I tried the approach that you suggested and instead of returning the
$43,123,598, which is the sum of all rows, it is returning $1,588,799,149The total contract value sums all the sales amount (contract value) from a sales table (each row in this table is one sale) and I exclude canceled and not active sales.
SD refers to how many days in the month we had sales
KD refers to how many days we expect to have sales
Example: Branch A had $100,000 in sales in june 20th and we expecting 30 days of sales in june.
$100,000 / 20 * 30 = $150,000
Branch B had $100,000 in sales in june 20th but only had sales in 19 out of the 20 days that has gone so far but it was projected to sell 29 days in june (because they missed one day).
$100,000/19 * 29 = $152,632
Basically PBi total assumes the sum os sales $200,000 and divide by 20 selling days and multiply by 30 and returns $300,000 instead of $302,632zEstimate IRCV2 = VAR TotalContractValue = CALCULATE ( SUM ( SalesReport[Contract Value] ), FILTER ( SalesReport, (SalesReport[Status] <> "Canceled" && SalesReport[Active] = "YES") ) ) VAR SD = [SD] VAR KD = [KD] RETURN SUMX(VALUES(Entity[Branch]),CALCULATE(DIVIDE(TotalContractValue,SD)*KD))
up
