Forum Discussion
How to calculate Addressable & Non-Addressable spend - based on different countries & different cate
Super User1) You need to create a gradation table for the regions. you don't need to link it to other tables.
gradation table
https://1drv.ms/x/s!AiUZ0Ws7G26Rhkln5gYPr-gUB05T?e=Tv5iPw
2) create separate key tables for regions and "category taxonomy" and link them to the fact table (main table)
3) create the necessary events.
1)
Amount Sales =
SUM ( 'Country Wise Spend'[Invoice Spend in NZD] )
2)
Addressable & Non-Addressable =
CALCULATE (
MAX ( 'DidRate'[MinSales] ),
TREATAS (
VALUES ( 'Dim Company Country'[Company Country] ),
DidRate[Country/Region]
))
3)
Calculate Addressable & Non-Addressable =
IF (
[Amount Sales]
>= [Addressable & Non-Addressable],
"YES",
"NO"
)
4)
CF = IF([Calculate Addressable & Non-Addressable]="YES","Turquoise","Pink")
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26Rhkp4STeOWch-JhG3?e=Ikxn5a
Hi, Thansk for the sending across the sample file. Just a small issue. I have two requirments when calculating addressable & non-addressable. Some categories are totally Non-addressable irrespective of the limit.
for ex: with australia , certain categories need to be taken off the addresable. thast why I had the matrix. For certain categories if the spend is less than 300k, then it is non-addressabel. I have added the comment below. Please let me know if you have any more questions. Thanks again for sharing the BI file. I have also attached the final result , how it looks like in the excel sheet. please refer to link below