Forum Discussion

raphazzz's avatar
raphazzz
Icon for Helper I rankHelper I
3 years ago

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 p...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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's avatar
    raphazzz
    Icon for Helper I rankHelper I
    3 years ago

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