Totals not calculating - Using Selected Value, IF statements and SUM/SUMX and/or Summarize

[Anonymous]

I'm having an issue writing a measure when I am using a selectedvalue function, a calculate function and a sum or sumx function.  I'm not sure if a Summarize function needs to be incorporated.

There are 3 measures I am trying to create and they expand from the previous, so I'm thinking if I can correct the base measure, the others will function without complex DAX.

It starts with a Fee.

Additional Fees =

IF(

SELECTEDVALUE( 'Budgeted Cost'[Cost Group] ) = "A",

CALCULATE( SUM( 'Item Price'[Price] ), USERELATIONSHIP( 'Item Price'[Site], Contract[Site] ) ),

IF(SELECTEDVALUE( 'Budgeted Cost'[Cost Group] ) = "B",

CALCULATE( SUM( 'Item Price'[Price] ), USERELATIONSHIP( 'Item Price'[Site], Contract[Site] ) ),

BLANK() ) )

I tried swapping SUMX.

Additional Fees 2 =

IF(

SELECTEDVALUE( 'Budgeted Cost'[Cost Group] ) = "A",

CALCULATE( SUMX('Item Price', 'Item Price'[Price] ), USERELATIONSHIP( 'Item Price'[Site], Contract[Site] ) ),

IF(SELECTEDVALUE( 'Budgeted Cost'[Cost Group] ) = "B",

CALCULATE( SUMX('Item Price', 'Item Price'[Price] ), USERELATIONSHIP( 'Item Price'[Site], Contract[Site] ) ),

I tried using variables:

Processing Fees 3 =

VAR A = CALCULATE( SUMX('Item Price', 'Item Price'[Price] ), USERELATIONSHIP( 'Item Price'[Site], Contract[Site] ) )

VAR B = CALCULATE( SUMX('Item Price', 'Item Price'[Price] ), USERELATIONSHIP( 'Item Price'[Site], Contract[Site] ) )

RETURN

IF(

SELECTEDVALUE( 'Budgeted Cost'[Cost Group] ) = "A", A,

IF(SELECTEDVALUE( 'Budgeted Cost'[Cost Group] ) = "B", B,

BLANK() ) )

I can't seem to get a total to work in any situation.

Basically I have a contract, with budgeted Groups A, B & C.

 

I then have my second measure:

Extended Fees = Fees * Quantity

I'm assuming if I fix the first measure) the total will just multiple across.  But I'm not sure if I will have the same problem with the third measure as they have similiar builds.

My third measure is :

Invoice Amt 1 =

IF( SELECTEDVALUE( 'Budgeted Cost'[Cost Group] ) = "A", SUM( 'Transactions'[Invoiced Amt ) - [Extended Fees],

IF( SELECTEDVALUE( 'Budgeted Cost_V'[Cost Group] ) = "B", [Extended Fees],

IF( SELECTEDVALUE( 'Budgeted Cost_V'[Cost Group] ) = "C", SUM( 'Accrual Transactions'[Invoice Amount] ),

BLANK() ) ) )

I haven't taken the time to build a sample dataset as my model has some complexity, and I'm afraid I will leave something out, but here is what the model looks like. (I hid some parts of the table names to align with formula names above)

I was reading on the forums that using a sumx can correct an incorrectly summing total, but it didn't help when I was using the IF statements.  I also read that using SUMX ( SUMMARIZE ( can help to populate the totals and actually in the Fees I almost don't want it to populate because it's not a total of 314.03, it's just an average of 157.03 across the lines, whereas on the Invoice Amt 1 I do want a total of 10998.72

I hope this is a easy DAX structure issue, otherwise I can try to create some sample data.  

 I basically want it to ideally look like this:

or:

Appreciate the help in advance.  Thank you.

[DataInsights · Accepted solution]

@Anonymous,

This is the basic pattern I use for calculating totals. There are various ways to define the virtual table (SUMMARIZE, CALCULATETABLE, VALUES, etc.). Once the virtual table is defined, use an iterator function like SUMX to calculate the total. If you want to suppress the total for a measure, use the function HASONEVALUE to determine if a single value for a column exists in the filter context (if one value exists, display the number; otherwise, display blank).

Total Measure =
VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE ( Table, Table[Column] ),
        “@Amount”, [Base Measure]
    )
VAR vResult =
    SUMX ( vTable, [@Amount] )
RETURN
    vResult

Here's a simpler way to write your measures:

Additional Fees =
SWITCH (
    SELECTEDVALUE ( 'Budgeted Cost'[Cost Group] ),
    "A",
        CALCULATE (
            SUM ( 'Item Price'[Price] ),
            USERELATIONSHIP ( 'Item Price'[Site], Contract[Site] )
        ),
    "B",
        CALCULATE (
            SUM ( 'Item Price'[Price] ),
            USERELATIONSHIP ( 'Item Price'[Site], Contract[Site] )
        )
)