topic Fix subtotal and total measure evaluated in differente levels of Hierarchy in DAX Commands and Tips

Fix subtotal and total measure evaluated in differente levels of Hierarchy

Anonymous — Mon, 22 Nov 2021 16:21:59 GMT

Good morning

I would really appreciate your help with this request:

Context:

I currently have a matrix within 3 levels in the rows and some measures

Rows :

Bank name (Category 1)

Account Type (Category 2)

Account Name ( Catgory 3)

What I need is to create a measure who evaluates the account type taking into account there are 2 options, and return an specific calculation different for each one. This last request i just solved it using Measure 1.

However, when I try to get the subtotals for Category 1 and the grand total, i just have not figured out how, because it must be sum of the results of evaluating the type of account, I mean the sum of the subtotals I got from Category 2

Table : Account Type

cod	name
1	COR
2	AHO

For identify wich type an account is, I used the code from the table above

Measure 1 :

VAR COR= [Measure23]-[Measure44]+[Measure45]

VAR AHO= [Measure22]-[Measure44]+[Measure45]

VAR AT = SUM(Account_Type_code)

RETURN

SWITCH ( Account_Type,

1,COR,2,AHO,3,"")

Here I need to get the right value that would be the sum of 26.498.449.893+7.430.738.545.94 , this per category and grand total.

Thanks.

Re: Fix subtotal and total measure evaluated in differente levels of Hierarchy

amitchandak — Tue, 23 Nov 2021 05:55:42 GMT

Anonymous , I think you need to use isinscope for that

if(isinscope (Table[Account Type]), [Measure1], [Measure2])

https://xxlbi.com/blog/new-dax-function-isinscope/

Re: Fix subtotal and total measure evaluated in differente levels of Hierarchy

Anonymous — Tue, 23 Nov 2021 13:01:09 GMT

Morning,

Thanks for your answer, isinscope coulw work too, but that part is already working in my modelo. the one I need is for the subtotal per category 1 that should return the result of evaluating the measure 1 + result of evaluating the measure 2.

Re: Fix subtotal and total measure evaluated in differente levels of Hierarchy

Anonymous — Mon, 29 Nov 2021 13:03:55 GMT

At the end, I use SUMMARIZE for creating a virtual table, within a new column where the condition were applied, the use SUMX in order to get the value I needed .

🙂