Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I created a new measure with the below code.
It basically creates a weighted average of Share Individuals 3+ based on BT.
The thing is that in my database, not all lines have a Share Individuals but all lines have a BT.
So the end result is wrong.
What I need is the same calculation that filters out the lines with no Share Individuals, so that the BT is not taken into account for the lines without Share Individuals.
Any idea how to integrate this?
Solved! Go to Solution.
Hi @Arthur_NS ,
I think your problem should be caused by _Part2. It sums all BT, so return incorrect result in divide. I suggest you to add a filter in __CATEGORY_VALUES. Here I create a sample to have a test.
Try this code.
Market share =
VAR __CATEGORY_VALUES =
FILTER (
VALUES ( 'MM'[Share Individuals 3+] ),
MM[Share Individuals 3+] <> BLANK ()
)
VAR __PART1 =
SUMX (
KEEPFILTERS ( __CATEGORY_VALUES ),
CALCULATE ( SUMX ( 'MM', 'MM'[Share Individuals 3+] * MM[BT] ) )
)
VAR __PART2 =
SUMX ( KEEPFILTERS ( __CATEGORY_VALUES ), CALCULATE ( SUM ( 'MM'[BT] ) ) )
RETURN
DIVIDE ( __PART1, __PART2 )
I think the result you want is (3*4+1*2+5*4+7*8)90 / 18 (4+2+4+8 ) = 5.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Not working, values are too low which leads me to believe too many lines are still taken into account
Shouldn't the MM BT be filtered as well?
Hi @Arthur_NS ,
I think your problem should be caused by _Part2. It sums all BT, so return incorrect result in divide. I suggest you to add a filter in __CATEGORY_VALUES. Here I create a sample to have a test.
Try this code.
Market share =
VAR __CATEGORY_VALUES =
FILTER (
VALUES ( 'MM'[Share Individuals 3+] ),
MM[Share Individuals 3+] <> BLANK ()
)
VAR __PART1 =
SUMX (
KEEPFILTERS ( __CATEGORY_VALUES ),
CALCULATE ( SUMX ( 'MM', 'MM'[Share Individuals 3+] * MM[BT] ) )
)
VAR __PART2 =
SUMX ( KEEPFILTERS ( __CATEGORY_VALUES ), CALCULATE ( SUM ( 'MM'[BT] ) ) )
RETURN
DIVIDE ( __PART1, __PART2 )
I think the result you want is (3*4+1*2+5*4+7*8)90 / 18 (4+2+4+8 ) = 5.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Arthur_NS , check if this exmaple can help
Market share =
VAR __CATEGORY_VALUES = VALUES('MM'[Share Individuals 3+])
RETURN
DIVIDE(
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(SUMX(filter('MM','MM'[Share Individuals 3+]<> 0),'MM'[Share Individuals 3+] *MM[BT]))
),
SUMX(KEEPFILTERS(__CATEGORY_VALUES), CALCULATE(SUM('MM'[BT])))
)
or
Market share =
VAR __CATEGORY_VALUES = VALUES('MM'[Share Individuals 3+])
RETURN
DIVIDE(
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(SUMX(filter('MM','MM'[Share Individuals 3+]<> 0 && not(isblank('MM'[Share Individuals 3+] )) ),'MM'[Share Individuals 3+] *MM[BT]))
),
SUMX(KEEPFILTERS(__CATEGORY_VALUES), CALCULATE(SUM('MM'[BT])))
)
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |