Forum Discussion
How to calculate Addressable & Non-Addressable spend - based on different countries & different cate
Hi All,
I have a requirment to calculate Addressable & Non-Addressable spend based on a few categories & rules. Based on the below table, Categories with Yes & Above spend threshold falls under addressable & categories with No & below threhold falls under non-addressable
example: if the spend is for Australia, INtercompany & spend for the category is 350K, then it is addressable
if teh spend is for Malaysia,Engineering & spend is 50K, then it is non-addressable
if the spend for Thailand , 3rd party manufacturing is 120K, then it is addresable.
I have attached the rules & the spend data in the excel sheet. Can you please let me know how can I get this in the dashboard, the final visual to be like this . The challenge is that the non-addressable & addressable rules are different for each country
Data Source Link : https://docs.google.com/spreadsheets/d/1UxzbITtz5jOeDdamlXzVyojPBUmhxGBh/edit?usp=share_link&ouid=113324457536568970847&rtpof=true&sd=true
Thanks in Advance
Manny
8 Replies
You are welcome.
1) 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=Ikxn5aHi, 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
You can explain this in detail why in two examples, How to calculate addressable and non-addressable
There are certain categories in certain countries which falls out of scope. Thays why if you see , in your example, you are right. Dairy products in Australia is non-addressabel, whereas in Saudi it is addressabel.
if the spend is any category falls under the threshold limit, that is also non addressable.
