Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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=11...
Thanks in Advance
Manny
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
Thanks Ashish, It worked. Also I can use your PBI file as a reference to create similar visuals in the future.
You are welcome.
You can explain this in detail why in two examples, How to calculate addressable and non-addressable
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
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.
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=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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |