Forum Discussion
Helper ICant get correct total on complex model
You're welcome,
Yes I tried it in the file you sent and it worked.
No worries.
Helper IHi tamerj1
Thanks for your reponse
Unfortunately it gives the same correct line value and 0 on total
Any thing else in mind ?
Community ChampionTry
Measure3 =
VAR SelectedDims =
ALLSELECTED (
GENERAL_ACCOUNT[DEPTID],
GENERAL_ACCOUNT[CLIENTID],
GENERAL_ACCOUNT[DOCDATE],
GENERAL_ACCOUNT[DOCNO]
)
RETURN
SUMX (
SUMMARIZE (
GENERAL_ACCOUNT,
GENERAL_ACCOUNT[BU],
GENERAL_ACCOUNT[Link],
GENERAL_ACCOUNT[ACCOUNTCODE],
GENERAL_ACCOUNT[SECTORID]
),
VAR Value1 =
CALCULATE (
SUMX ( GENERAL_ACCOUNT, [Measure1] ),
SelectedDims,
--acc
USERELATIONSHIP ( DimLink[LinkCode], ACCOUNTS[Link] ),
USERELATIONSHIP ( DimBU[BUCode], ACCOUNTS[BU] ),
--dep
USERELATIONSHIP ( DimLink[LinkCode], DEPARTMENTS[Link] ),
USERELATIONSHIP ( DimBU[BUCode], DEPARTMENTS[BU] ),
--sector
USERELATIONSHIP ( DimLink[LinkCode], SECTORS[Link] ),
USERELATIONSHIP ( DimBU[BUCode], SECTORS[BU] ),
--general acc
USERELATIONSHIP ( DimLink[LinkCode], GENERAL_ACCOUNT[Link] ),
USERELATIONSHIP ( DimBU[BUCode], GENERAL_ACCOUNT[BU] )
)
RETURN
IF ( Value < 0, 0, Value1 )
)
- Mostafa-Hussien
SECTORID is not a column on the GENERAL_ACCOUNT table. GENERAL_ACCOUNT table has a DEPTID column that has an active relationship many to one on SECTOR table.
So instead I tried adding GENERAL_ACCOUNT[DEPTID] on the SUMMARIZE, and this time it returned a higher value for the positive value, and unfortunately the total is still 0
I appreciate your response a lot. Any other methods to try ?
- Mostafa-Hussien
I uploaded the pbix file here
https://www.dropbox.com/s/uxr3htuaxrz9hk8/d3-test.pbix?dl=0
This contains the relevant tables and my original measures and the result that I am not able to alter. I really appreciate if you can help me with this riddle