Forum Discussion
How to implement amount KPI and ratio KPI
Hi specialists! Please help me..
I want to implement amount KPI and ratio KPI in one report by matrix design.
How to implement correct ratio KPI?
ex.
filter: category
<matrix 1>
sales amount
cost amount
<matrix 2>
cost rate
*When category A.
filter: category A
<matrix 1>
sales amount 100
cost amount 50
<matrix 2>
cost rate 50%
*When category B.
filter: category B
<matrix 1>
sales amount 100
cost amount 40
<matrix 2>
cost rate 40%
*When category ALL.
<matrix 1>
sales amount 200
cost amount 90
<matrix 2>
cost rate 90% <-----------NG
YamaTaka.
Hi, Anonymous
You can try the following methods.
Measure:
cost rate = IF ( SELECTEDVALUE ( 'Table'[category] ) = BLANK (), DIVIDE ( SUM ( 'Table'[cost amount] ), SUM ( 'Table'[sales amount] ) ), DIVIDE ( SELECTEDVALUE ( 'Table'[cost amount] ), SELECTEDVALUE ( 'Table'[sales amount] ) ) )Is this the output you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
How about below? How to implement DAX?
*When category A.
filter: category A
<matrix > amount | ratio(/sales)
sales amount 100 | 100%
cost amount 50 | 50%
*When category B.
filter: category B
<matrix > amount | ratio(/sales)
sales amount 100 | 100%
cost amount 40 | 40%
*When category ALL.
<matrix > amount | ratio(/sales)
sales amount 200 | 100%
cost amount 90 | 45%
v-zhangtiCommunity Support
Hi, Anonymous
You can try the following methods.
Measure:
cost rate = IF ( SELECTEDVALUE ( 'Table'[category] ) = BLANK (), DIVIDE ( SUM ( 'Table'[cost amount] ), SUM ( 'Table'[sales amount] ) ), DIVIDE ( SELECTEDVALUE ( 'Table'[cost amount] ), SELECTEDVALUE ( 'Table'[sales amount] ) ) )Is this the output you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.