Forum Discussion

mclawler's avatar
mclawler
Icon for Helper III rankHelper III
2 years ago
Solved

Previous Year Dateadd Excluding Data

When I use this measure:   VAR AdjustedTable =     ADDCOLUMNS(         VALUES('PwBi LoanData'[Product (groups)]),         "AdjustedValue",         IF(             'PwBi LoanData'[Product (gro...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi mclawler ,
    For your total and Auto outside the value is not correct, we can use the ISINSCOPE function, the total for a correct sum, Auto outside the value of some can not get, we can try to use a few more defined variables to get a valid result, I hope my solution is helpful to you!

    AmountBooked COUNT(Copy) = 
VAR AutoCount=CALCULATE(
COUNT('AutoLoanMatrixDummy'[AmountAdvanced]),'LoanDataDummy'[Product (groups)]="Auto")
VAR OtherCount=COALESCE(
CALCULATE(COUNT(LoanDataDummy[Amount Booked]),ALLEXCEPT('LoanDataDummy','LoanDataDummy'[Product (groups)])),0)
VAR C=CALCULATE(COUNT(LoanDataDummy[Amount Booked]),FILTER('LoanDataDummy','LoanDataDummy'[Product (groups)]="Auto"))
RETURN
IF(
    ISINSCOPE('LoanDataDummy'[Product (groups)]),
    SWITCH(
        TRUE(),
        MAX('LoanDataDummy'[Product (groups)])="Auto",
        AutoCount,
        OtherCount),
        AutoCount+OtherCount-C)



     

     I have updated your data to the results you need, if you have any other questions you can check the PBIX file I have uploaded, I would be grateful if my solution can help you solve your problem!

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

mclawler's avatar
mclawler
Icon for Helper III rankHelper III
2 years ago

Hi Rita!! 

ProductionTableDummy.pbix

Here is the dummy pbix that should align with the table below once accurate.  I have noticed that if you highlight multiple months in the slicer then some of the RV loans appear, but you shouldn't have to do that based on the specific measure columns:

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi mclawler ,
For your total and Auto outside the value is not correct, we can use the ISINSCOPE function, the total for a correct sum, Auto outside the value of some can not get, we can try to use a few more defined variables to get a valid result, I hope my solution is helpful to you!

AmountBooked COUNT(Copy) = 
VAR AutoCount=CALCULATE(
COUNT('AutoLoanMatrixDummy'[AmountAdvanced]),'LoanDataDummy'[Product (groups)]="Auto")
VAR OtherCount=COALESCE(
CALCULATE(COUNT(LoanDataDummy[Amount Booked]),ALLEXCEPT('LoanDataDummy','LoanDataDummy'[Product (groups)])),0)
VAR C=CALCULATE(COUNT(LoanDataDummy[Amount Booked]),FILTER('LoanDataDummy','LoanDataDummy'[Product (groups)]="Auto"))
RETURN
IF(
    ISINSCOPE('LoanDataDummy'[Product (groups)]),
    SWITCH(
        TRUE(),
        MAX('LoanDataDummy'[Product (groups)])="Auto",
        AutoCount,
        OtherCount),
        AutoCount+OtherCount-C)



 

 I have updated your data to the results you need, if you have any other questions you can check the PBIX file I have uploaded, I would be grateful if my solution can help you solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • mclawler's avatar
    mclawler
    Icon for Helper III rankHelper III
    2 years ago

    WOW!!  This is incredible, thank you so much!  Works perfectly!! 

  • mclawler's avatar
    mclawler
    Icon for Helper III rankHelper III
    2 years ago

    Continuation of this issue.  I have used this same measure within another table only changing the data sources, and now I can't seem to get the Total Row to Sum correctly.  Any ideas? 

     

    Current Measure being used:

     

    OB COUNT(Copy) =
    VAR AutoCount=CALCULATE(SUM('OutstandingBalancesMaster'[Count])) + CALCULATE(COUNT('PwBi CIPL'[Balance]),OutstandingBalancesMaster[Type Description Column] = "Auto")
    VAR OtherCount=COALESCE(
    CALCULATE(SUM(OutstandingBalancesMaster[Count]),ALLEXCEPT('OutstandingBalancesMaster',OutstandingBalancesMaster[Type Description Column])),0)
    VAR C=CALCULATE(SUM('OutstandingBalancesMaster'[Count]),FILTER('OutstandingBalancesMaster',OutstandingBalancesMaster[Type Description Column] = "Auto"))
    RETURN
    IF(
        ISINSCOPE(OutstandingBalancesMaster[Type Description Column]),
        SWITCH(
            TRUE(),
            MAX(OutstandingBalancesMaster[Type Description Column]) = "Auto",
            AutoCount,
            OtherCount),
            AutoCount+OtherCount-C) + 0

    The individual row results are correct, just the Total is incorrect. 
     

     

    # Total should be 23,917 
    $ Total should be $514,771,284
     
    It appears to now be doubling all row sums except for Auto.
    Still works great for the previous table I used this formula on, just this one is doubling up