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.
Hello all!
I have been working some time with Power BI and more simple DAX formulas. Now I have a problem that I need support with:
I have three tables (Masterdata, Projectspent and Link Project to Country)
Masterdata:
Projectnumber Description
P100 New Tomatochopper
P200 New Cucumberchopper
P300 New Pineappleslicer
Projectspent
Projectnumber Month Spenttype Actuals
P100 6 Directcost 3000
P100 6 Other 4000
P200 6 Directcost 4000
P200 6 Other 3000
P300 6 Directcost 5000
P300 6 Other 2000
Link Project to Country
Country Region Projectnumber Percentage
France South P100 60%
Germany North P100 40%
France South P200 100%
Germany North P300 50%
Spain South P300 20%
France South P300 30%
There is a 1:many relation from “Link Project to Country” to “Masterdata”
There is a 1:many relation from Masterdata to Projectspent
The purpose of my application is to see how much each project should be allocated to each country or Region (Percentage column determines how much should be allocated. Total allocation is always 100%.
How do I get Power BI to show data in correct way? I cannot get the DAX formula to work in a correct way.
Thanks!
FredO
Solved! Go to Solution.
Hi, @FredO
It’s my pleasure to answer for you.
According to your description,if you only want country and region in the visual ,you need to create a measure with 'summarize'.
Like this:
Allocation =
SUMX (
ADDCOLUMNS (
SUMMARIZE (
LinkProjecttoCountry,
[Country],
[Region],
[Projectnumber],
[Percentage],
"aaa",
SUMX (
FILTER (
ALL ( Projectspent ),
[Projectnumber] = EARLIER ( LinkProjecttoCountry[Projectnumber] )
),
[Actuals]
)
),
"allocation", [aaa] * [Percentage]
),
[allocation]
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @FredO
It’s my pleasure to answer for you.
According to your description,if you only want country and region in the visual ,you need to create a measure with 'summarize'.
Like this:
Allocation =
SUMX (
ADDCOLUMNS (
SUMMARIZE (
LinkProjecttoCountry,
[Country],
[Region],
[Projectnumber],
[Percentage],
"aaa",
SUMX (
FILTER (
ALL ( Projectspent ),
[Projectnumber] = EARLIER ( LinkProjecttoCountry[Projectnumber] )
),
[Actuals]
)
),
"allocation", [aaa] * [Percentage]
),
[allocation]
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try this measure:
Project Allocation =
VAR vProject =
MAX ( Masterdata[Projectnumber] )
VAR vActuals =
SUM ( Projectspent[Actuals] )
VAR vPercent =
MAX ( 'Link Project to Country'[Percentage] )
VAR vResult = vActuals * vPercent
RETURN
vResult
Create table visual, using Projectnumber from Masterdata table.
Proud to be a Super User!
Hello it worked fine, but when I remove projectnumber and description I get strange totals!
br
Fredrik
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 |
---|---|
111 | |
109 | |
89 | |
76 | |
67 |
User | Count |
---|---|
125 | |
111 | |
100 | |
83 | |
71 |