Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

manny_mahendran_1-1679375540449.png

 

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

 

manny_mahendran_0-1679375431000.png

Data Source Link : https://docs.google.com/spreadsheets/d/1UxzbITtz5jOeDdamlXzVyojPBUmhxGBh/edit?usp=share_link&ouid=11...

 

Thanks in Advance

Manny

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks Ashish, It worked. Also I can use your PBI file as a reference to create similar visuals in the future. 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ahmedx
Super User
Super User

You can explain this in detail why in two examples, How to calculate addressable and non-addressable
Screen Capture #721.pngScreen Capture #722.png

That is when we refer tot eh matrix on teh right. yes means it is addressable, no means non-addressable. this only refers to the category.

 

the threshold limit is the second factor

tempsnip.png

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.

Ahmedx
Super User
Super User

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
1.png2.png3.png
2) create separate key tables for regions and "category taxonomy" and link them to the fact table (main table)
4.png4.png6.png
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

 

https://docs.google.com/spreadsheets/d/1MhB5D-jykpY3SR_i0MIXn7NDqR0XP25Z/edit?usp=share_link&ouid=11...

 

manny_mahendran_0-1679438666694.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.