Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
My need is to report the Spend per Material Code so that only those material codes which have been bought at least two different countries during the selected time period are included. Material codes are country specific and the link between countries is the GTIN code, which connect country specific material codes to each other. I emphasize I need a measure, because calculation needs to be dynamic depending on selected time frame (calculated columns are not enough). Simplified sample data is following:
Material Code | GTIN | Buying Country | Spend | Year | ||||
Material1111 | EAN1234 | SE | 1000 | 2020 | ||||
Material1118 | EAN1234 | DK | 10000 | 2021 | ||||
Material1114 | EAN1235 | DK | 2000 | 2021 | ||||
Material1121 | EAN1235 | DK | 11000 | 2021 | ||||
Material1117 | DK | 3000 | 2020 | |||||
Material1124 | EAN1236 | DE | 12000 | 2021 | ||||
Material1120 | EAN1237 | DK | 4000 | 2020 | ||||
Material1127 | EAN1237 | SE | 13000 | 2021 | ||||
Material1123 | EAN1238 | DE | 5000 | 2022 | ||||
Material1113 | EAN1238 | SE | 14000 | 2022 | ||||
Material1126 | EAN1239 | DE | 6000 | 2020 | ||||
Material1116 | EAN1239 | DK | 15000 | 2022 | ||||
Material1130 | SE | 7000 | 2020 | |||||
Material1119 | EAN1240 | DK | 16000 | 2022 | ||||
Material1112 | EAN1241 | SE | 8000 | 2021 | ||||
Material1122 | EAN1241 | DE | 17000 | 2022 | ||||
Material1115 | EAN1242 | DK | 9000 | 2021 | ||||
Material1125 | EAN1242 | DE | 18000 | 2022 | ||||
Material1128 | SE | 19000 | 2022 | |||||
Material1129 | EAN1253 | SE | 20000 | 2022 |
I have used two measure in addition to those columns.
Solved! Go to Solution.
Try
Cross border GTINs Spend =
VAR Virtual_Table =
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( 'Table'[Material GTIN] ),
"No_GTIN_Countires", [NumOfCountries]
),
KEEPFILTERS ( NOT ISBLANK ( 'Table'[Material GTIN] ) ),
REMOVEFILTERS ( 'Table'[Material Code] )
)
VAR vFilteredTable =
FILTER ( Virtual_Table, [No_GTIN_Countires] > 1 )
RETURN
CALCULATE ( SUM ( 'Table'[Spend] ), vFilteredTable )
Hi,
Thanks a lot @johnt75 !
That was in a way so simple and in the other hand so brilliant solution. Maybe I had a insufficient understanding of Removeflters. "Material code" was not even present in measure, so it was not so obvious for me. This help a lot!
Try
Cross border GTINs Spend =
VAR Virtual_Table =
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( 'Table'[Material GTIN] ),
"No_GTIN_Countires", [NumOfCountries]
),
KEEPFILTERS ( NOT ISBLANK ( 'Table'[Material GTIN] ) ),
REMOVEFILTERS ( 'Table'[Material Code] )
)
VAR vFilteredTable =
FILTER ( Virtual_Table, [No_GTIN_Countires] > 1 )
RETURN
CALCULATE ( SUM ( 'Table'[Spend] ), vFilteredTable )
Hi,
Thanks a lot @johnt75 !
That was in a way so simple and in the other hand so brilliant solution. Maybe I had a insufficient understanding of Removeflters. "Material code" was not even present in measure, so it was not so obvious for me. This help a lot!
User | Count |
---|---|
129 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
190 | |
97 | |
67 | |
63 | |
54 |