Forum Discussion
Cant get correct total on complex model
I made a measure on a fact table "General Account" that works fine. It is basically like this
Measure1 =
SUMX(
FILTER(GENERAL_ACCOUNT,
LEFT(GENERAL_ACCOUNT[ACCOUNTCODE],5) = "21031"),
(GENERAL_ACCOUNT[DEBITVALUE] - GENERAL_ACCOUNT[CREDITVALUE])
)The problem comes when I want to aggregate only positive values of this measure and display it in a table/matrix using dimension tables columns that have some active and some inactive relationships
The SUMX I use in my attempt to filter only positive values works on line values but not on total
Here is the first I tried without filtering positive values, which returns correct line and total values
Measure2 =
CALCULATE(
SUMX(
GENERAL_ACCOUNT,
[Measure1]
),
ALLSELECTED(GENERAL_ACCOUNT[DEPTID], GENERAL_ACCOUNT[CLIENTID], GENERAL_ACCOUNT[DOCDATE], GENERAL_ACCOUNT[DOCNO]),
--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])
)And here is my attempt for filtering on positive values which failed on total line
Measure3 =
var int_talbe =
ADDCOLUMNS(
SUMMARIZE(GENERAL_ACCOUNT,
GENERAL_ACCOUNT[BU],
GENERAL_ACCOUNT[Link],
GENERAL_ACCOUNT[ACCOUNTCODE]),
"value",
CALCULATE(
SUMX(
GENERAL_ACCOUNT,
[Measure1]
),
ALLSELECTED(GENERAL_ACCOUNT[DEPTID], GENERAL_ACCOUNT[CLIENTID], GENERAL_ACCOUNT[DOCDATE], GENERAL_ACCOUNT[DOCNO]),
--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
SUMX(
int_table,
IF([value]<0,0,[value])
)Here is how both measures behave
Any help?
You're welcome,
Yes I tried it in the file you sent and it worked.
No worries.
24 Replies
- MohammadLoran25
Solution Sage
Hi Mostafa-Hussien ,
I think it is because of the ALLSELECTED function that you have in Measure3.
Please comment it and let me know the result.
Regards,
Loran
- Mostafa-Hussien
Helper I
I cannot drop the ALLSELETED(), otherwise it will not aggregate regardless of the ALLSELECTED arguments.
I mean; I added certain columns to ALLSELECTED like CLIENTID, because I want an aggregate value regardless of CLIENTID
- MohammadLoran25
Solution Sage
Not the best solution, but as an easy and fast solution to fix it, please try this as well. It should work for your total:
Measure = VAR _TABLEE = ADDCOLUMNS ( SUMMARIZE ( GENERAL_ACCOUNT, GENERAL_ACCOUNT[BU], GENERAL_ACCOUNT[Link], GENERAL_ACCOUNT[ACCOUNTCODE] ), "@measure3", [measure3] ) RETURN SUMX ( _TABLEE, [@measure3] )
- tamerj1
Community Champion
please try
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]
),
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
Helper I
Hi tamerj1
Thanks for your reponse
Unfortunately it gives the same correct line value and 0 on total
Any thing else in mind ?
- tamerj1
Community Champion
Try
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 )
)