Calculated Measure gives different result when I use a column vs a calculated column in the formula

[dospencer]

I have a calculated column that is concatenating a code and a title in a table as below:

Organization = SWITCH(TRUE(), 'DWH Finance_Organization_Dim'[Organization_Code] IN {"FA","NETTUI","PCTDIS","SA","TUIREV"},'DWH Finance_Organization_Dim'[Title],'DWH Finance_Organization_Dim'[Organization_Code]&" "&'DWH Finance_Organization_Dim'[Title])

I also have a measure defined that I use in a matrix visual but I get different results when I use the "Organization" calculated column vs the "Title" column in the Matrix visual.

When the measure is defined as shown below and I use the "Title" column in the visual, I get the correct results but when I use "Organization" in the visual I get 0. I have even replaced Title with "Organization" in the CALCULATE filter and still doesn't work:

_subtotalNetTuition =

CALCULATE(SUM('ODS Net_Tuition'[Tuition_Billed])

, ALL('DWH Finance_Organization_Dim'[Title])

,'DWH Finance_Organization_Dim'[Line_Order] <= 5)

What am I doing wrong?

[v-angzheng-msft · Accepted solution]

Hi, @dospencer 

Try to modify your measure like this:

__Selected Measure = 
VAR _maxline =
    MAX ( 'DWH Finance_Organization_Dim'[Line_Order] )
VAR _subtotalTuitionRevenue =
    CALCULATE (
        SUM ( 'ODS Net_Tuition'[Tuition_Billed] ),
        // ALL( 'DWH Finance_Organization_Dim'[Title]),
        ALL( 'DWH Finance_Organization_Dim'),
        'DWH Finance_Organization_Dim'[Line_Order] <= 3
    )
VAR _subtotalNetTuition =
    CALCULATE (
        SUM ( 'ODS Net_Tuition'[Tuition_Billed] ),
        // ALL ( 'DWH Finance_Organization_Dim'[Title] ),
        ALL ( 'DWH Finance_Organization_Dim' ),
        'DWH Finance_Organization_Dim'[Line_Order] <= 5
    )
VAR _subtotalFinancialAid =
    CALCULATE (
        SUM ( 'ODS Net_Tuition'[Tuition_Billed] ),
        ALL ( 'DWH Finance_Organization_Dim' ),
        // 'DWH Finance_Organization_Dim'[Title] = "Financial Aid Awards"
        'DWH Finance_Organization_Dim'[Title] = "Financial Aid"
    )
VAR _percentDiscount =
    DIVIDE ( _subtotalFinancialAid, _subtotalTuitionRevenue, 0 )
VAR _subtotal =
    SWITCH (
        TRUE (),
        _maxline = 3, _subtotalTuitionRevenue,
        // _maxline = 4, _subtotalFinancialAid * -1,
        _maxline = 4, _subtotalFinancialAid * 1,
        _maxline = 5, _subtotalTuitionRevenue - _subtotalFinancialAid,
        _maxline = 6
            && NOT ISBLANK ( _percentDiscount ), FORMAT ( _percentDiscount, "Percent" ),
        SUM ( 'ODS Net_Tuition'[Tuition_Billed] )
    )
RETURN
    _subtotal

Result:

Please refer to the attachment below for details.

You can read the following blog to learn more about the ALL function
https://radacad.com/how-to-use-all-in-a-dax-expression-in-power-bi

https://blog.enterprisedna.co/how-to-use-the-all-function-in-power-bi-dax/

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[v-angzheng-msft]

Hi, @dospencer 

Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Zeon Zheng

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

[dospencer]

@v-angzheng-msft ,

Thank you.

Here's the additional information you requested. 

The Dimension table: DWH Finance_Organization_Dim

Organization_Key	Organization_Code	Title	Line_Order
1	210	Undergraduate Tuition & Fees	1
2	SA	Study Abroad	2
3	TUIREV	Tuition Revenue	3
4	FA	Financial Aid	4
5	NETTUI	Net Tuition	5
6	PCTDISC	Percent Discount	6

The fact table: ODS Net_Tuition

College	Term	Organization_Key	Fiscal_Year	Tuition_Billed
My College	Fall	1	2022	200
My College	Fall	1	2021	300
My College	Fall	1	2022	400
My College	Fall	2	2022	100
My College	Fall	2	2021	50
My College	Fall	4	2022	100
My College	Fall	4	2022	100
My College	Fall	3	2022	0
My College	Fall	5	2022	0
My College	Fall	6	2022	0
My College	Fall	3	2021	0
My College	Fall	5	2021	0
My College	Fall	6	2021	0

The tables are joined via the Organization_Key column.

Expected results with the "Organization" calculated column:

My College
Fall 2022
210 - Undergraduate Tuition & Fees	$600
Study Abroad	$100
Tuition Revenue	$700
Financial Aid	$200
Net Tuition	$500
Percent Discount	28.57%
Spring 2022
...

Actual results with the "Organization" calculated column:

My College
Fall 2022
210 - Undergraduate Tuition & Fees	$600
Study Abroad	$100
Tuition Revenue	$0
Financial Aid	$200
Net Tuition	$500
Percent Discount	0.0%
Spring 2022

Here's the DAX for the measure that is placed in the visual and works if i use the Title in Rows of the visual but doesn't work if I use the Organization calculated column:

Selected Measure =

VAR _maxline =

MAX ('DWH Finance_Organization_Dim'[Line_Order] )

VAR _subtotalTuitionRevenue =

CALCULATE(SUM('ODS Net_Tuition'[Tuition_Billed])

, ALL('DWH Finance_Organization_Dim'[Title])

,'DWH Finance_Organization_Dim'[Line_Order] <= 3)

VAR _subtotalNetTuition =

CALCULATE(SUM('ODS Net_Tuition'[Tuition_Billed])

, ALL('DWH Finance_Organization_Dim'[Title])

,'DWH Finance_Organization_Dim'[Line_Order] <= 5)

VAR _subtotalFinancialAid =

CALCULATE(SUM('ODS Net_Tuition'[Tuition_Billed])

, ALL('DWH Finance_Organization_Dim')

,'DWH Finance_Organization_Dim'[Title] = "Financial Aid Awards")

VAR _percentDiscount =

DIVIDE(_subtotalFinancialAid,_subtotalTuitionRevenue,0)

VAR _subtotal =

SWITCH(TRUE(),

     _maxline = 3, _subtotalTuitionRevenue

     ,_maxline = 4, _subtotalFinancialAid*-1

        ,_maxline = 5, _subtotalTuitionRevenue - _subtotalFinancialAid

        ,_maxline = 6 && NOT ISBLANK ( _percentDiscount ),

            FORMAT ( _percentDiscount, "Percent" )

        ,SUM('ODS Net_Tuition'[Tuition_Billed])

        )

RETURN _subtotal

[johnt75]

Use Performance Analyzer to get the DAX generated for the visual and compare between the versions which use Title and Organization. It is possible that more filters are being applied when you use Organization, perhaps for a sort by column, and you may need to remove those as well as the filter on Organization.