Totals not showing for measures

[hideakisuzuki01]

Hi, 

I have the below data, 

And I need to compare different versions of budget (there are only two in the screenshot but there are more in real data).

There are two Slicers and users choose "Budget1" and "Budget2" in each Slicer for this example.

What I need is the below.

I wrote the below DAX 

----------------------------------------------------------------------------------

M_Negative3 =

var SelectedProduct = SELECTEDVALUE('Fact'[Product])

var SelectedRegion = SELECTEDVALUE('Fact'[Region])

var SelectedVersion1 = SELECTEDVALUE('BudgetVersion1'[BugetVersion])

var SelectedVersion2 = SELECTEDVALUE('BudgetVersion2'[BugetVersion])

var value1 =

CALCULATE(

    SUM('Fact'[Cost]),

     'Fact'[Product] = SelectedProduct,

     'Fact'[Region] = SelectedRegion,

     'Fact'[BugetVersion] = SelectedVersion1)

var value2 =

CALCULATE(

    SUM('Fact'[Cost]),

     'Fact'[Product] = SelectedProduct,

     'Fact'[Region] = SelectedRegion,

     'Fact'[BugetVersion] = SelectedVersion2)

var value3 = value1 - value2

var value4 = IF(value3 < 0, value3, 0)

return value4

-------------------------------------------------------------------------------------------------------

I see correct numbers for each row but there is no total as below. I know the Totals are not working because SELECTEDVALUE functinos return blank for the Totals, but I cannot think of any way of geting this to work any other way.

What can I do to display totals here ?

Regards

[hideakisuzuki01]

It worked just as I expected, thanks !

And you did it so quickly too, I am amazed at your solution in such a short amount of time.

I can't thank you enough, 

Btw, do  you provide a consulting service? if you do, I am willing to pay for it when I need help in the future.

[danextian]

Hi @hideakisuzuki01 

There is no total because you used SELECTEDVALUE which if there are more than one distinct values will return blank and  this is very likely at the total level be in columns or rows. Also, there is no need to use SELECTEDVALUE in the context of your measure if the filter is coming from the fact or a related dimensions table.

The measure below a ssumes that BudgetVersion doesnt have a relationship to your fact table.

M_Negative3 =
VAR SelectedVersion1 =
    SELECTEDVALUE ( 'BudgetVersion1'[BugetVersion] )
VAR SelectedVersion2 =
    SELECTEDVALUE ( 'BudgetVersion2'[BugetVersion] )
VAR value1 =
    CALCULATE ( SUM ( 'Fact'[Cost] ), 'Fact'[BugetVersion] = SelectedVersion1 )
VAR value2 =
    CALCULATE ( SUM ( 'Fact'[Cost] ), 'Fact'[BugetVersion] = SelectedVersion2 )
VAR value3 = value1 - value2
VAR value4 =
    IF ( value3 < 0, value3, 0 )
RETURN
    value4

To  get the t otal sum:

SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'Fact', 'Fact'[Product], 'Fact'[Region] ),
        "@M", [M_Negative3]
    ),
    [@M]
)

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Cost budget version 1: = 
CALCULATE ( SUM ( cost[cost] ), budget_version[budget_version] = "b01" )

Cost budget version 2: = 
CALCULATE ( SUM ( cost[cost] ), budget_version[budget_version] = "b02" )

Positive: = 
VAR _t =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( cost, 'product'[product], region[region] ),
            "@result", [Cost budget version 1:] - cost[Cost budget version 2:]
        ),
        [@result] > 0
    )
RETURN
    SUMX ( _t, [@result] )

Negative: = 
VAR _t =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( cost, 'product'[product], region[region] ),
            "@result", [Cost budget version 1:] - cost[Cost budget version 2:]
        ),
        [@result] < 0
    )
RETURN
    SUMX ( _t, [@result] )