MinX in a table

Hi experts,

Wanted to see if I can find a particular value based on a condition.

I tried to use Minx function but have no clue how to move forward. Attached is the pbix.

Problem statement:

  1. I am looking for the threshold value when I reach 80% of the total sale. for e.g, the one highlighted in yellow then I want to show that in a card. I want to show $25,405 in a card.
  2. And the number at which it occurred, in this case, it was 193. Which mean there are 193 customers which are core customers.

I want to show that in a card, so it becomes easier to show that you need to have that much of sales to be the core customer. I just need to show the sales amount and how many people are in that threshold.

Any help is appreciated.

Thanks,
Ritesh

Hi @rit372002, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Your dimension table is pretty huge about 4x of your fact table, which is causing any code I write to be extremely slow.

I must laugh a little at myself here as I had my sign the wrong way ([Sales RT PCT] > 0.8) - otherwise I would’ve gotten back sooner. :laughing:

The below code will give you what you need.

VAR vCustomers =
    ADDCOLUMNS(
        SUMMARIZE( 'CPP POS', GROWER[CRM_PARENT_GROWER_MASTER_KEY] ),
        "@Total Sales", [Total Sales],
        "@Sales RT PCT", [Sales RT PCT]
    )
RETURN
    MINX( FILTER( vCustomers, [@Sales RT PCT] < 0.8 ), [@Total Sales] )

2 Likes

@AntrikshSharma

Thanks for pointing it out. I reduced the size by 90%

@samaguire Thank you so much! One more ask, is it possible to show only till this amount in the table?

My guess is, users want to just export this table in excel so they want to see only till this amount and those customers.

Thanks again! it was a big headache’.

Ritesh

Have you tried adding a filter to the visual?

Where can I add the filter?

Can I add a filter based off a dynamic measure value? something like this?

Total Sales <= [measure 5

Thanks,
Ritesh

You could add a filter in the filter pane, there is a section for the selected visual.

No, you wouldn’t be able to do that. But you could do Sales RT PCT is less than 0.8.

Have a play, and if you’re having issues raise another question in the forum :wink:

No, it didn’t work :frowning:

I will raise another question. Thanks again for your help. It helped me understand SUMMARIZE and ADDCOLUMNS functions.

thanks,
Ritesh

I was just finishing having a play myself.

It does work, but there are issues:

  1. Power BI runs slow
  2. Sales RT PCT calculates based on visible customers in the matrix, so needs to be reworked

Unfortunately, I don’t have any more time to spend on this. Hopefully, someone picks it up in a new question.

Please don’t forget to mark a solution to the original problem you had.

:wink:

I am not sure how did you reach to 100% on the last visual but it’s ok. Thanks again for your help.

I marked that as solution. Have a nice day. :slight_smile:

Thanks,
Ritesh