Forum Discussion

steveplatz's avatar
steveplatz
Frequent Visitor
6 years ago
Solved

ALLSELECTED Duplicating Table Output

Hi everyone,   I've got a problem that I can't seem to find a resolution to that I'm hoping you can help with. What I'm trying to do is calculate the MAX value for all selected rows and display tha...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi steveplatz 

    You could just adjust it as below:

    Step1:

    Adjust the formula as below:

    New MaxSelectedSales = CALCULATE(MAX(FactSales[Sales]),ALLSELECTED(FactSales),VALUES(DimMarket[MarketName]))
    Step2:
    change the cross filter direction from "Single" to "Both"

     

    Result:

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

kentyler's avatar
kentyler
Solution Sage
6 years ago

This what my measure ended up like

Max Sales per Market =
VAR cur_Market =
    SELECTEDVALUE ( Sales[MarketId] )
VAR total_sales_per_market =
    CALCULATE ( MAX ( Sales[Sales] ), ALL ( Sales ), Sales[MarketId] = cur_Market )
RETURN
    total_sales_per_market

in a problem like this the first step is to get the value you want to filter by out of the current filter context-- in this case SELECTEDVALUE() does that-- and store it in a variable

the next step is to do your calculation-- inside the CALCULATE() I use ALL() to remove any filters on the Sales table coming from the external filter context (like the one from the table visual that gets the values for a specific row) and use the saved variable as a filter to get only the rows I want the MAX of

 

 

Power BI is easy to learn, but it has hard parts.
I'm a personal Power Bi Trainer I learn something every time I answer a question

 

steveplatz's avatar
steveplatz
Frequent Visitor
6 years ago

kentyler Thanks for the detailed response. That makes sense and works if I'm looking for the max per market, but what I'm trying to accomplish is to get the MAX value of all selected records, taking any outside filters into account. This is a simplified example with only the IsCurrent and Market filters, but there could be much more.

 

Do you know how I would accomplish that?

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    You can change to ALLSELECTED

    Max Sales per Market = VAR cur_Market = SELECTEDVALUE(Sales[MarketId])
    VAR total_sales_per_market = CALCULATE(max(Sales[Sales]),ALLSELECTED(Sales),Sales[MarketId]=cur_Market) return total_sales_per_market
    That seems to take both a store slicer and an iscurrent slicer into account
     
    • steveplatz's avatar
      steveplatz
      Frequent Visitor
      6 years ago

      This works when filtered to a specific market, but not when all markets are displayed. I changed the MAX for "Market 2" to be different than Market 1 and get this result:

       

       

      What I'd like to see is 140 for every cell in MaxSelectedSales2.

       

      • kentyler's avatar
        kentyler
        Solution Sage
        6 years ago

        Maybe you need to remove the filter on just one market in line 3.