Forum Discussion

rbks's avatar
rbks
Frequent Visitor
2 years ago
Solved

Top 1 with 2 measures.

I got fact table that contains names, sales rate, outlet names.  I got a dimension table contains name list.  And I got 2 measure. One of them counting distinct outlet names. Other one average of s...
Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    Hello rbks,

     

    Can you please try this:

    Top Name with Conditions = 
VAR OutletThreshold = 50
VAR FilteredNames = FILTER(
    ADDCOLUMNS(
        SUMMARIZE(
            'FactTable', 
            'DimensionTable'[Name], 
            "TotalOutlets", [Total outlet number], 
            "AvgRate", [AVG Rate]
        ),
        "Country", RELATED('DimensionTable'[Country])
    ),
    [TotalOutlets] > OutletThreshold
)
VAR TopName = TOPN(
    1, 
    FilteredNames, 
    [AvgRate], 
    DESC
)
VAR ResultName = MAXX(TopName, 'DimensionTable'[Name])

RETURN
ResultName

    Hope this helps!

Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
2 years ago

Hello rbks,

 

Can you please try this:

Top Name with Conditions = 
VAR OutletThreshold = 50
VAR FilteredNames = FILTER(
    ADDCOLUMNS(
        SUMMARIZE(
            'FactTable', 
            'DimensionTable'[Name], 
            "TotalOutlets", [Total outlet number], 
            "AvgRate", [AVG Rate]
        ),
        "Country", RELATED('DimensionTable'[Country])
    ),
    [TotalOutlets] > OutletThreshold
)
VAR TopName = TOPN(
    1, 
    FilteredNames, 
    [AvgRate], 
    DESC
)
VAR ResultName = MAXX(TopName, 'DimensionTable'[Name])

RETURN
ResultName

Hope this helps!

rbks's avatar
rbks
Frequent Visitor
2 years ago

I just delete ADDCOLUMNS and "Country", RELATED('DimensionTable'[Country]) It worked perfect. Thanks a lot!