Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a sales table that shows date, sales and marketID. I then have another table which shows marketID and countryname.
My tables are joined by marketID to retrieve the countryname. However, for any marketID that isn't matched in the market table, I would like to flag these as 'Other'. Is this possible?
Update: my market table also has a conversion figure that I use to apply to the sales figure. For any markets in the sales table that don't appear in the market table, I would like these to be grouped under 'other' so that I can convert the sales figure by the 'Other' conversion figure.
Thanks!
Solved! Go to Solution.
Column =
VAR _conversionrate = RELATED(Market[conversionfigure])
RETURN IF(_conversionrate=BLANK(),1,_conversionrate)
Instead of Country put the conversion figure column name and in place of other put the default number that you want
Hi @bo_afk ,
I am not sure if I have missed something in your query, but perhaps in the query editor you could create a new Conditional Column called 'New Market ID' and, use the formula:
= Table.AddColumn(#"Expanded Sheet2", "Custom", each if [#"Sheet2.Countryname "] = null then "Other" else [Market ID])
This would then be able to replace your original Market ID column.
The way I tested this was to create two tables as you described, and merged the queries with a Left Outer Join.
"Sheet2" was the Market ID, Countryname table you described
Hope this helps, or if I not fully understanding your question, please let me know?
Kind regards,
Paul
Hi @paulvans182 @Anonymous , thanks for your resposes. There is an update to my query.
My market table also has a conversion figure that I use to apply to the sales figure. For any markets in the sales table that don't appear in the market table, I would like these to be grouped under 'other' so that I can convert the sales figure by the 'Other' conversion figure.
Does this change anything to the suggestions you provided?
Thanks again!
Column =
VAR _conversionrate = RELATED(Market[conversionfigure])
RETURN IF(_conversionrate=BLANK(),1,_conversionrate)
Instead of Country put the conversion figure column name and in place of other put the default number that you want
@bo_afk Hope there is many to 1 relationship between Sales and market table please create following calculated column in sales table
Column =
VAR _country = RELATED(Market[Country])
RETURN IF(_country=BLANK(),"Other",_country)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |