Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

MIN function that is controlled by a slicer

Hello. Can you please help me with the below issue. I cannot seem to find anyone who can solve this.

 

I have been struggling to find a solution to my problem for some time now.

Below is a sample picture of my data source. I am trying to get BI to find the lowest price for each Order# and have another slicer that tells BI which Sellers to include when trying to find Min charge for each order.

BI com.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

Ideally, I would like to have a slicer showing all the seller options (A-D) and BI calculates the MIN cost for each order number based on the sellers included. 

 

Example: If I have sellers A & C selected in my slicer the MIN cost for each order # would be as follows.

Also need to calculate the total cost of the filtered results.

Order #               Seller              Cost

1                          C                    300

2                          A                    250

3                          A                    1700

 

Total Cost: 2250

Need Help
Tutorial Requests

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi jblack,

     

    I'm afraid it's difficult to achieve that. Based on my test, allselected(table) or allselected(table, table[column]) will be implemented after allexcept.

     

     

    Regards,

    Jimmy Tao

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Jimmy,

       

      I actually found a solution to this problem yesterday. 

      To find the min value I used: Low Cost Measure =MINX(values(table1[seller]),CALCULATE(min(table1[cost])))

      For total cost of the min values I used:

      IF(

           HASONEVALUE (table1[table1[cost]),

           [low cost measure],

           SUMX(ALL(table1[Order#]),[low cost measure])

       

      Now I am getting the desired result I was looking for. However, using these measure's I havent found a way to show the Seller associated with each cost for each order. So, still some work to be done. 

       

      Let me know if you can crack that one!