Forum Discussion

pacificnwp's avatar
pacificnwp
Frequent Visitor
11 months ago
Solved

How do I create a global max value that references another measure?

I found a very similar post regarding visitor counts, but I can't seem to make the solution work for my report.   I have a flattened sales data set (no tables), with each sales rep having a row of ...
Need Help
  • MasonMA's avatar
    MasonMA
    11 months ago

    pacificnwp 

    Hi, there would be two solutions.

    One with DAX, first create a Calculated Column

    QuarterKey = 'Sales'[Year] * 10 + 'Sales'[Quarter]

    then create 3 DAX Measures

    Quarters to Date = 
CALCULATE (
    DISTINCTCOUNT ( 'sales'[QuarterKey] ),
    ALLEXCEPT ( 'sales', 'sales'[Name] )
)

     

    MaxQuarters = 
MAXX(
    ADDCOLUMNS(
        VALUES('Sales'[Name]),
        "RepQuarters", CALCULATE(
            DISTINCTCOUNT('Sales'[QuarterKey]),
            ALL('Sales')
        )
    ),
    [RepQuarters]
)

     

    IsActive = 
IF ( [Quarters to Date] = [MaxQuarters], 1, 0 )

    apply them on table and you will see 'Mike Smith' should be filtered out. 

     

     

    Or in Power Query, you can use below logic to filter out the above two person as well.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZSxCoMwFEV/RTI7tMnzDzoJTg4dxCGV0AZjUqr/T1NQCr7W5A5ClHPJeeGarhO19qa4BCNK0T7NYLWz8xJfzvGRJynX5aStF32ZxUuQVyBPAK9AfwX6K9Bfgf4E+hPoT6A/gf4V6F/t/Rs7mqKd7PLYJ+SfhmYl8D0UnCAowZqalfgsXQjjTQ8jlsL2waZnLU8mWM+zEtgcW9ex82J/yNU6V9TBmzl+bbTXd/NamV91TOMSw0EZysdZBdM4619eBNgBGJfV7hhnnUvjgDvYG3axHuPbvQqcPbuK0/h33P4N", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Role Title" = _t, Area = _t, Year = _t, Quarter = _t, #"Calc Schema" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
    Custom1 = Table.SelectRows(#"Changed Type", each  [Calc Schema] = "main" and [Year] >= 2023),

    // Step 2: keep unique Rep/Quarter Year
    UniqueRepQuarter = Table.Distinct( Table.SelectColumns(Custom1, {"Name", "Year","Quarter"}) ),
    // Step 3: group by rep
    Grouped = Table.Group(
        UniqueRepQuarter,
        {"Name"},
        {{"QuarterCount", each Table.RowCount(_), Int64.Type}}
    ),
    // Step 4: find max quarters
    MaxQuarters = List.Max(Grouped[QuarterCount]),

    // Step 5: keep only active reps
    ActiveReps = Table.SelectRows(Grouped, each [QuarterCount] = MaxQuarters),
    // Step 6: re-join to original filtered data
    Result = Table.NestedJoin(
        Custom1,
        {"Name"},
        ActiveReps,
        {"Name"},
        "Active",
        JoinKind.Inner
    ),
    Expanded = Table.ExpandTableColumn(Result, "Active", {"QuarterCount"})
in
    Expanded

     

    Calc Schema and Year can be adjusted at this step, 

    Custom1 = Table.SelectRows(#"Changed Type", each [Calc Schema] = "main" and [Year] >= 2023)

    if i'm using the above conditions i'll have below result at 'ActiveRep' step.

     

     

     

MasonMA's avatar
MasonMA
Icon for Super User rankSuper User
11 months ago

 

pacificnwp 

 

Hi,

COUNTROWS(FILTER('Sales', 'Sales'[Quarter])) is not really filtering anything, it just counts all rows in the current filter context. As a measure, it cannot be used directly in a calculated column because measures are evaluated in filter context, not row context.

 

The correct approach would be creating a Measure to count the number of rows for a sales rep under the current filter contex

Measure1 =
COUNTROWS('Sales')

 

another Measure to return the maximum number of quarters any rep has

Measure2 =
MAXX(
VALUES('Sales'[SalesRep]),
[Measure1]
)

 

Then use a flag measure as visual filter so that shows only rows where Is Active Rep = 1. 

IsActiveRep =
IF([Measure1] = [Measure2], 1, 0)

 

 

  • pacificnwp's avatar
    pacificnwp
    Frequent Visitor
    11 months ago

    Thanks for your suggested solution. I created Measure1 and 2 as suggested above, however they are both generating the same results, whereas I would expect measure2 to display the max value on every line, even if that sales rep had a lower number.

    • MasonMA's avatar
      MasonMA
      Icon for Super User rankSuper User
      11 months ago

      This should be able to ensure the count is done per rep, ignoring other filters like quarter.

      Measure1 =
CALCULATE(
    COUNTROWS('Sales'),
    ALLEXCEPT('Sales', 'Sales'[SalesRep])
)

      For Measure2 to calculate Max Quarter my best guess is to use the approach GeraldGEmerick suggested but with VALUE() since it's a single column. 

      Measure2 =
MAXX(
    ADDCOLUMNS(
        VALUES('Sales'[SalesRep]),
        "RepQuarters", [Measure1])
    ),
    [RepQuarters]
)

       

      Please share a small sample of your flattened data just a few rows for 2–3 reps? That way other users can test the measures directly and show you the expected outputs row by row.

      • pacificnwp's avatar
        pacificnwp
        Frequent Visitor
        11 months ago

        Below is a screenshot of a subset of sample data.  My report filters out the orange rows (only keeping calc schema = main, and 2023 forward), but I included them here for visibility.  It seems some of the DAX formulas I've tried pick some of those rows up, but I don't want them included in the results.