Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 | 10 | 45 |
1 | B | 15 | 45 |
1 | C | 20 | 45 |
2 | A | 5 | 30 |
2 | B | 10 | 30 |
2 | C | 15 | 30 |
This is what i need based on my current table.
I have this measure which works:
Change Val Total = CALCULATE(SUM('Table1'[Value]), ALLEXCEPT('Table1', 'Table1'[Project], 'Table1'[Date]))
Solved! Go to Solution.
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.
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.
@paulfink , where is date,
It should be like
Sub Total = CALCULATE(SUM('Table1'[Value]), ALLEXCEPT('Table1', 'Table1'[Project]))
@amitchandak I have already tried that and it does not work.
I need the value to be a measure so please reference it as Value1.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
@paulfink , That is an image. Not the fastest way. I need to create data myself?
updated
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |