Forum Discussion
paulfink
5 years agoPost Patron
Spread total over rows
Hi guys, I need have a measure that takes the value depending on the progress of a task. I need to take them totals and spread it across a project. Project Task Value DESIRED 1 A ...
- 5 years ago
Hi, paulfink
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Value(Measure):
Value = var _pro = SELECTEDVALUE('Table'[Project]) var _task = SELECTEDVALUE('Table'[Task]) return SWITCH( _pro, 1, SWITCH( _task, "A",10, "B",15, "C",20 ), 2, SWITCH( _task, "A",5, "B",10, "C",15 ) )You may create a measure like below.
Desired = var tab = SUMMARIZE( ALL('Table'), 'Table'[Project], 'Table'[Task], "Value", var _pro = 'Table'[Project] var _task = 'Table'[Task] return SWITCH( _pro, 1, SWITCH( _task, "A",10, "B",15, "C",20 ), 2, SWITCH( _task, "A",5, "B",10, "C",15 ) ) ) return SUMX( FILTER( tab, [Project]=SELECTEDVALUE('Table'[Project]) ), [Value] )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.
v-alq-msft
5 years agoCommunity Support
Hi, paulfink
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Value(Measure):
Value =
var _pro = SELECTEDVALUE('Table'[Project])
var _task = SELECTEDVALUE('Table'[Task])
return
SWITCH(
_pro,
1,
SWITCH(
_task,
"A",10,
"B",15,
"C",20
),
2,
SWITCH(
_task,
"A",5,
"B",10,
"C",15
)
)
You may create a measure like below.
Desired =
var tab =
SUMMARIZE(
ALL('Table'),
'Table'[Project],
'Table'[Task],
"Value",
var _pro = 'Table'[Project]
var _task = 'Table'[Task]
return
SWITCH(
_pro,
1,
SWITCH(
_task,
"A",10,
"B",15,
"C",20
),
2,
SWITCH(
_task,
"A",5,
"B",10,
"C",15
)
)
)
return
SUMX(
FILTER(
tab,
[Project]=SELECTEDVALUE('Table'[Project])
),
[Value]
)
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.