How to fix a Non-addictive calculation?

[raphazzz]

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

[raphazzz]

up

[amitchandak]

@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
))

[raphazzz]

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,149

The 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,632

zEstimate 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))