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.
Hi All
Can you help with following?
I have the following main table
Sales | ||
Month | OU | Sales |
Jan | Asia | 204 |
Jan | Europe | 234 |
Jan | Pacific | 211 |
Feb | Asia | 456 |
Feb | Europe | 233 |
Feb | Pacific | 123 |
March | Asia | 345 |
March | Europe | 321 |
March | Pacific | 179 |
and a sub table ( wherein asia in broken into 3 parts by %)
Sub Region | (Main region is Asia) |
South East Asia | 40% |
ME | 35% |
North | 25% |
I want a sales measure for sub region ( or calculated column if its a better approach)
the solution shold be like
Sales | ||
Month | OU | Solution |
Jan | South East Asia | 40% of 204 |
Jan | ME | 35% of 204 |
Jan | North | 25% of 204 |
Feb | South East Asia | |
Feb | ME | |
Feb | North | |
March | South East Asia | |
March | ME | |
March | North |
of course these are samples, actual table are quite big
Solved! Go to Solution.
Hi, @luisClive
You may modify the measure as below. The pbix file is attached in the end.
Result =
var tab =
ADDCOLUMNS(
CROSSJOIN(
DISTINCT(Sales[Month]),
DISTINCT(Sub[Sub Region])
),
"Result",
var month = [Month]
var subregion = [Sub Region]
var s =
CALCULATE(
SUM(Sales[Sales]),
FILTER(
ALL(Sales),
[Month]=month&&
[OU]="Asia"
)
)
var v = LOOKUPVALUE(Sub[(Main region is Asia)],Sub[Sub Region],subregion)
return
s*v
)
return
SUMX(
tab,
[Result]
)
Result:
Best Regards
Allan
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Hi, @luisClive
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Sales:
Sub:
You may create a measure as below.
Result =
var s =
CALCULATE(
SUM(Sales[Sales]),
FILTER(
ALL(Sales),
[Month]=MAX(Sales[Month])&&
[OU]="Asia"
)
)
var v = LOOKUPVALUE(Sub[(Main region is Asia)],Sub[Sub Region],MAX(Sub[Sub Region]))
return
s*v
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @luisClive
You may modify the measure as below. The pbix file is attached in the end.
Result =
var tab =
ADDCOLUMNS(
CROSSJOIN(
DISTINCT(Sales[Month]),
DISTINCT(Sub[Sub Region])
),
"Result",
var month = [Month]
var subregion = [Sub Region]
var s =
CALCULATE(
SUM(Sales[Sales]),
FILTER(
ALL(Sales),
[Month]=month&&
[OU]="Asia"
)
)
var v = LOOKUPVALUE(Sub[(Main region is Asia)],Sub[Sub Region],subregion)
return
s*v
)
return
SUMX(
tab,
[Result]
)
Result:
Best Regards
Allan
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Many thanks for help
@luisClive , Create a new table like.
filter(crossjoin(sales,sub),sales[OU] =sub[region])
crossjoin need all columns to different across table, so if they are same you can rename usinf select columns
refer
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
did not work for me
this part is not clear
sales[OU] =sub[region])
Not working because of no unique values on both tables?
all columns are different for me
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |