Calculate Count & Sum to 1 Row of a Matrix Table Only

[mclawler]

Hi there, I'm going to try and explain this as simply as possible.  I have this matrix table built:

From a separate data table, I need to to account for this negative count and negative sum from the Auto row of the above table.

I can't figure out how to incorporate this negative count/sum into the Auto group row ONLY.  I can't seem to isolate the Auto row.  Any measure I create affects all of the other rows (Marine, Motorcycle, Quad, RV/ Shareback) also, no matter what I try.  

Thank you for your time, this forum has helped me so much in the past!  

[Ritaf1983 · Accepted solution]

Hi @mclawler,

The condition should be placed before the calculation. I didn't understand the logic of the transformation from 35 to 345, but I can still demonstrate the logic for manipulating the "Auto" category versus other categories.

The measure can use the following logic:
Test =
IF(
SELECTEDVALUE('Table'[Category]) = "Auto",
SUM('Table'[Value]) - SUM('Table 2'[Value]),
SUM('Table'[Value])
)

The pbix with the example is attached

 

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

[mickey64]

For your reference.

Measure = SUMX(FILTER('Table','Table'[#]<0 && 'Table'[Column]="Auto"),'Table'[#])

[mclawler]

This is what I have so far but it's adding too many to Auto, all other rows still count normal as they should but instead of -35 it's like -345 to Auto.  It's almost like it's now disregarding my DateTable slicer for the Collateral count. 

SUMX(OutstandingBalancesMaster,IF(OutstandingBalancesMaster[Type Description Column] = "Auto",OutstandingBalancesMaster[OB_Count] + 'PwBi CIPL'[SoldFeesCount],OutstandingBalancesMaster[OB_Count]))

Let me put it another way to see if you can replicate on your desktop

Table 1 :

Auto = 23733

Marine = 2

Motorcycle = 7

Quad = 2

RV = 102

Table 2:

Collateral = 35

End result needs to be Table 3:

Auto = 23768

Marine = 2

Motorcycle = 7

Quad = 2

RV = 102

[Ritaf1983]

Hi @mclawler 
You can use selectedvalue function , like :
if(selectedvalue(category) = "Auto", abc,xyz)
guide for using selectedvalue() here :

https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/

For more detailed suggestion 

please share a pbix or some dummy data that keeps the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

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

[mclawler]

It won't allow me to use the IF statement and I'm not sure why?  Maybe cause I'm building a measure and not a column?  

This is what I have so far but it's adding too many to Auto, all other rows still count normal as they should but instead of -35 it's like -345 to Auto.  It's almost like it's now disregarding my DateTable slicer for the Collateral count. 

SUMX(OutstandingBalancesMaster,IF(OutstandingBalancesMaster[Type Description Column] = "Auto",OutstandingBalancesMaster[OB_Count] + 'PwBi CIPL'[SoldFeesCount],OutstandingBalancesMaster[OB_Count]))

Let me put it another way to see if you can replicate on your desktop

Table 1 :

Auto = 23733

Marine = 2

Motorcycle = 7

Quad = 2

RV = 102

Table 2:

Collateral = 35

End result needs to be Table 3:

Auto = 23768

Marine = 2

Motorcycle = 7

Quad = 2

RV = 102

[Ritaf1983 · Accepted solution]

Hi @mclawler,

The condition should be placed before the calculation. I didn't understand the logic of the transformation from 35 to 345, but I can still demonstrate the logic for manipulating the "Auto" category versus other categories.

The measure can use the following logic:
Test =
IF(
SELECTEDVALUE('Table'[Category]) = "Auto",
SUM('Table'[Value]) - SUM('Table 2'[Value]),
SUM('Table'[Value])
)

The pbix with the example is attached

 

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

[mclawler]

I do have a new issue however, my Total row isn't accurate:

The Total row should = 23,865

Please advise 🙂

[Ritaf1983 · Accepted solution]

Hi @mclawler 
Modify the formula to :

Test =

VAR AdjustedTable =

    ADDCOLUMNS(

        VALUES('Table'[Category]),

        "AdjustedValue",

        IF(

            [Category] = "Auto",

            CALCULATE(SUM('Table'[value])) - CALCULATE(SUM('Table 2'[Value])),

            CALCULATE(SUM('Table'[value]))

        )

    )

RETURN

SUMX(AdjustedTable,[AdjustedValue])

it will fix the total 

The updated pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[mclawler]

Any idea why this measure would be dropping the Motorcycle row data?  It keeps the Auto & RV data but drops Motorcycle row data:

This is the Measure I'm using:

VAR AdjustedTable =

    ADDCOLUMNS(

        VALUES('PwBi LoanData'[Product (groups)]),

        "AdjustedValue",

        IF('PwBi LoanData'[Product (groups)] = "Auto",

    CALCULATE(TOTALYTD(SUM(AutoLoanMatrix[AmountAdvanced]),'DateTable'[Date])),

    CALCULATE(TOTALYTD(SUM('PwBi LoanData'[Amount Booked]),'DateTable'[Date]))))

RETURN

SUMX(AdjustedTable,[AdjustedValue]) + 0


Does the same thing with PreviousMonth/Year etc... 

[mclawler]

That worked perfectly, absolutely amazing.  Thank you so much! 

[Ritaf1983]

Happy to help 🙂

[mclawler]

It worked!!!!!!  Thank you so much!!!!