Forum Discussion
Row and column wise aggregation where same sourcing country can be part of different hierarchy.
Hi All,
These are the measures to calculate a column value named as FE over months on hierarchy {category>format>brand>packsizedescription>sourcing country} and maintaing a row level aggregation but in case of column aggregation rather than calculating FE column aggregation it is calculating absolute difference between BDM FC(CS) and Actual (SC)
"Main measure to create column FE": Measure FE Agg(SC) = Var _A=TODAY()
Var _B= DATE(YEAR(_A),MONTH(_A),"01")
Var _Fa=IF(MIN('Calendar'[Date])< _B,IF([FE Agg (SC)]=BLANK(),BLANK(),[FE Agg (SC)]))
Return
_Fa
Supporting/helper measures:
FE Agg (SC) =
VAR IsMonthInScope = ISINSCOPE('Calendar'[MonthNameShort])
RETURN
IF(
NOT(IsMonthInScope),
SUMX(VALUES('Calendar'[MonthNameShort]), [FE Agg Monthly]),
[FE Agg Monthly]
)
FE Agg Monthly =
IF(
ISINSCOPE('PBI 2- FA SKU Customer Report'[Sourcing Country]),
[Base FE],
IF(
ISINSCOPE('PBI 2- FA SKU Customer Report'[PacksizeDescription]),
SUMX(VALUES('PBI 2- FA SKU Customer Report'[Sourcing Country]), [FE Sourcing Country]),
IF(
ISINSCOPE('PBI 2- FA SKU Customer Report'[Brand]),
SUMX(VALUES('PBI 2- FA SKU Customer Report'[PacksizeDescription]), [FE Packsize]),
IF(
ISINSCOPE('PBI 2- FA SKU Customer Report'[Format]),
SUMX(VALUES('PBI 2- FA SKU Customer Report'[Brand]), [FE Brand]),
IF(
ISINSCOPE('PBI 2- FA SKU Customer Report'[Category]),
SUMX(VALUES('PBI 2- FA SKU Customer Report'[Format]), [FE Format]),
[Base FE]
)
)
)
)
)
FE Sourcing Country =
IF(
ISINSCOPE('PBI 2- FA SKU Customer Report'[Sourcing Country]),
[Base FE],
BLANK()
)
FE Packsize =
IF(
ISINSCOPE('PBI 2- FA SKU Customer Report'[PacksizeDescription]),
SUMX(VALUES('PBI 2- FA SKU Customer Report'[Sourcing Country]), [FE Sourcing Country]),
BLANK()
)
FE Brand =
IF(
ISINSCOPE('PBI 2- FA SKU Customer Report'[Brand]),
SUMX(VALUES('PBI 2- FA SKU Customer Report'[PacksizeDescription]), [FE Packsize]),
BLANK()
)
FE Format =
IF(
ISINSCOPE('PBI 2- FA SKU Customer Report'[Format]),
SUMX(VALUES('PBI 2- FA SKU Customer Report'[Brand]), [FE Brand]),
BLANK()
)
Base FE =
Var T=TODAY()
Var D= DATE(YEAR(T),MONTH(T),"01")
VAR ActualSum = SUM('PBI 2- FA SKU Customer Report'[Actual(CS)])
VAR BDMFCSum = SUM('PBI 2- FA SKU Customer Report'[BDM FC (CS)])
RETURN
IF(
MIN('Calendar'[Date])< D,ABS(ActualSum - BDMFCSum),Blank()
)
2 Replies
- lbendlin
Super User
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523The data I'm using is confidential and I'm bound to not even share a sample set of the same. If you want some specific understanding of the data set and what challenges currently in my path I can share that.
Thanks in advance
