Sum higher level in hierarchy based on measure on lower hierarchy

[Ingan]

DimReport

Report	ReportRow	Account	SignType	UniqueKey
Rapport129	129/* CM	35100	1	4177960
Rapport129	129/* CM	35100	-1	4174475
Rapport129	129/* CM	34000	1	4176966
Rapport129	129/* CM	93220	-1	4179774

Bridge

UniqueKey	Account
4179774	93220
4177960	35100
4176966	34000
4174475	35100

Fact

FactKey	Account	Amount	Sum(Sign)	Amount*Sign
1	35100	10	0	0
2	35100	20	0	0
3	34000	100	1	100
4	34000	200	1	200
5	93220	-500	-1	500
6	93220	300	-1	-300
		130		500

I have placed the measures here to show how it´s related to rows in Fact.

Sum(Sign) is the sum of Sign from DimReport

Model

I can´t use RELATED to get the corresponding sign into the fact table since one account can have multiple signs ( see Account 35100 )

I have a hierarchy with 3 levels in a dimension.

"129/*CM" is second level and third level is Account

Account can have a Sign with 1 or -1 and if we are standing on level 3 I want to multiply Amount with Sign.

Amount*Sign:= IF(HASONEFILTER(DimRapport[Account]),[SumAmount]*[Sign], [SumAmount])

SumAmount := sum(Amount)

I now want the sum on level 2 (129/*CM) to sum up the Amount*Sign values, not the SumAmount values. But my obstacle is that in my Amount*Sign measure I have said that level 3(Account) has to be filtered.

I should mention that this have been solved previously in MDX with CALCULATIONPASSVALUE but my knowledge of MDX is zero, and i can´t figure out how this can be translated into DAX.

MDX code:

Scope(

       {

            [MEASURES].[SumAmount]

       }

);            

this

  =

case [DimReport].[DimReport].Currentmember.level.ordinal

    when 3 then

        CALCULATIONPASSVALUE([Measures].Currentmember * [DimReport].[DimReport].currentmember.Properties( "Sign" ),4 )

    else

        CALCULATIONPASSVALUE([Measures].Currentmember ,4)

    end;            

End Scope;    

How can I get the sum on level 2 to sum up the values from Amount*Sign? The sum should be

500 and not 130

Thanks in advance for your time!

[lbendlin]

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Ingan]

I have added the data in tables and updated with more information.

I get the HTML error though and i have tried to post a new question as described in link but i still get the headers wrong. Is it possible to view the data anyhow?

[lbendlin]

Doesn't look like the bridge table is required?

[Ingan]

This is an old existing solution in multidim cube so i dont have all background, but i agree that maybe it would be same result if we just had a relation directly between fact and dimreport.