DAX

[Anonymous]

Hi,

I need to calculate sum of $ amounts in column X based on condition GroupBy in column Y, Z.

Ex:

X          Y     Z

$5       A      IND

$6       A       US

$7       B       US

$8       C       CAN

$4       C       IND

$6       B       IND

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to your description, create the following virtual table.

Out_result =
SUMMARIZE (
    ABC,
    ABC[GL Description],
    "A",
        IF (
            NOT ISBLANK (
                CALCULATE (
                    SUM ( ABC[YTD] ),
                    FILTER (
                        ALL ( ABC ),
                        MAX ( ABC[GL Description] ) = ABC[GL Description]
                            && ABC[Group] = "CA"
                    )
                )
            ),
            CALCULATE (
                SUM ( ABC[YTD] ),
                FILTER (
                    ALL ( ABC ),
                    MAX ( ABC[GL Description] ) = ABC[GL Description]
                        && ABC[Group] = "CA"
                )
            ),
            0
        )
)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[Anonymous]

Thanks for your help on this. However, when use the calculation formula as above, I get an error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

[v-henryk-mstf]

Hi @Anonymous ,

Is the formatting used correctly in the formula you created? During my testing, this problem was not found. Did you refer to the pbix file I provided? If there is still confusion, please provide screenshots of the relevant test results.

Looking forward to your reply.

Best Regards,
Henry

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

[Anonymous]

Hello,

I am trying to calculate sum based on columns from two different tables (Actuals & MYR). I am getting the correct output for YTDActuals. But, for "YTD MYR" its not. I used the below formula for YTD Actuals:

YTD Actuals = CALCULATE(SUMX(Actuals,Actuals[Doc Amt (USD)])). But, when I am using similar formula with reference to table "MYR" and specific column in the table, its giving total sum (same for all rows) instead of giving for each row category. Any help is greately appreciated.

Note: I mapped the tables Actuals & MYR based on common columns.

[ryan_mayu]

@Anonymous 

what's the expected output based on your sample data?

[Anonymous]

Hi,

The expected output is:

Sum of column X, groupby rows in column Y and Z

Thank you!

[ryan_mayu]

@Anonymous 

is the sample data or expected output in your first post? It does not like the Y and Z can be grouped.

maybe you can try

calculate(sum(x), allexcept(table, y,z))

[amitchandak]

@Anonymous , Expected output is not clear

example

calculate(sum(Table[X]), filter(Table, Table[Y] = "A" && Table[Z] = "IND" ))

[Anonymous]

Sorry, Let me share the expected output clearly and the current table structure

The table "ABC" 

Column A	Column B	YTD	GL Description	Group
		$40	Conversion	CA
		$30	Cosourcing	PD
		$80	Fees	CA
		$50	Conversion	IK
		$10	Cosourcing	MA
		$20	Conversion	CA

Output should be as below only for Group "CA"

GL Description	YTD
Conversion	$60
Cosourcing	$0
Fees	$80

[ryan_mayu]

@Anonymous 

I missed this reply.

Measure 2 = CALCULATE(sum(ABC[YTD]),FILTER(ABC,ABC[Group]="CA"))+0