Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aabha123
Regular Visitor

DAX for level of detailing

Table1.PNGData.PNG     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 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1651223034531.png

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.

View solution in original post

5 REPLIES 5
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1651223034531.png

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.

aabha123_0-1652820352323.png

 

Hi @aabha123 ,

What did you get when you using ALL function.

Best Regards,
Community Support Team _ kalyj

 

aabha123
Regular Visitor

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. 

 

Link to pbix file 

 

I hope you have understood my query. 

amitchandak
Super User
Super User

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors