Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I want to find out the level of distribution of %ROI for my each benefit and investment category. In the given data, type column has benefit and invest whose substraction leads to ROI and to perform the substraction i used sign flag. Now my %ROI formula should be (New ROI / sum(invest) *100) for which i have tried the DAX with divide and calculate function but somehow i am unable to get the level of detail or the %ROI distribution. In simple words, how the 11% is distributed towards benefits or invest ? Please help me with this. Thank you in advance. @DAX @roi
Solved! Go to Solution.
Hi @aabha123 ,
You just need to add ALL function in the ROI% measure, if not, according to context, for the Benefit rows,
Filter(ROI_newtest,ROI_newtest[Type]="Invest") will return nothing.
Modify the formula like this:
ROI% =
DIVIDE (
ROI_newtest[New ROI],
CALCULATE (
SUM ( ROI_newtest[Value] ),
FILTER ( ALL ( ROI_newtest ), ROI_newtest[Type] = "Invest" )
),
0
)
Get the expected result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @aabha123 ,
You just need to add ALL function in the ROI% measure, if not, according to context, for the Benefit rows,
Filter(ROI_newtest,ROI_newtest[Type]="Invest") will return nothing.
Modify the formula like this:
ROI% =
DIVIDE (
ROI_newtest[New ROI],
CALCULATE (
SUM ( ROI_newtest[Value] ),
FILTER ( ALL ( ROI_newtest ), ROI_newtest[Type] = "Invest" )
),
0
)
Get the expected result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for reaching out and sharing the sample file which shows the exact distribution of ROI% distribution over flag and description. However when i tried the same approach and DAX with the actual data I am unable to see the % distribution over description. Please find the added screenshot with reference. FYI, i tried using ALL function but no luck so far. Please suggest. Thank you in advance.
Hi @aabha123 ,
What did you get when you using ALL function.
Best Regards,
Community Support Team _ kalyj
Hello @amitchandak Thanks for the reply.
Unfortunalely your given suggestions didnt work.
Hereby i am attaching the sample input with output (ROI and ROI%) along with the obix file.
I hope you have understood my query.
@aabha123 , in place for filter, try keepfilters(ROI_newTest[type] ="Invest")
or
filter(all(ROI_newTest[type]), ROI_newTest[type] ="Invest")
or
filter(all(ROI_newTest), ROI_newTest[type] ="Invest")
rest should remain same
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |