Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I'd appreciaet your advice
I have 3 SharePoint lists (parent-chil) I pulled them into PBI like a table and created relationship in data model
the $$ amounts are related to parent not the child, so basically users enter their total domain cost and then provide some domain breakdown, but when I create a matrix and a measure of sum, the measure gets duplicated for every single sub domain.
how can I prevent that?
Solved! Go to Solution.
Hi, @Anonymous ;
You could create a measure as follow:
amount =
var _res=IF (
MAX ( 'Sub-Domain'[Sub-Domain] )
= CALCULATE (
MIN ( 'Sub-Domain'[Sub-Domain] ),
FILTER (
ALL ( 'Sub-Domain' ),
[Parent:Main Table ID] = MAX ( 'Main_Table'[Main Table ID] )
)
),
CALCULATE (
SUM ( 'Sub-Domain'[Amount $] ),
FILTER (
ALL ( 'Sub-Domain' ),
[Parent:Main Table ID] = MAX ( 'Main_Table'[Main Table ID] )
)
)
)
return IF(ISINSCOPE('Main_Table'[Main Table ID]),_res,SUM('Sub-Domain'[Amount $]
))
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous ;
You could create a measure as follow:
amount =
var _res=IF (
MAX ( 'Sub-Domain'[Sub-Domain] )
= CALCULATE (
MIN ( 'Sub-Domain'[Sub-Domain] ),
FILTER (
ALL ( 'Sub-Domain' ),
[Parent:Main Table ID] = MAX ( 'Main_Table'[Main Table ID] )
)
),
CALCULATE (
SUM ( 'Sub-Domain'[Amount $] ),
FILTER (
ALL ( 'Sub-Domain' ),
[Parent:Main Table ID] = MAX ( 'Main_Table'[Main Table ID] )
)
)
)
return IF(ISINSCOPE('Main_Table'[Main Table ID]),_res,SUM('Sub-Domain'[Amount $]
))
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Don't rely on the implicit measures. Write a custom measure that handles the computations according to your requirements.
User | Count |
---|---|
54 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
80 | |
57 | |
40 | |
19 | |
10 |