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 sales rate. 

I want to calculate Top 1 name that has max of sales rate and outlet number should be greater than 50. 

 

I create some measures but I couldn't find the right solution. When I use TOPN function it works but I got country slicer. If the name is not from the selected country, it shows blank. Also I tried with filter pane. I couldn't use 2 different contion for top 1. Can someone help me?

 

This is sample of my table.

NameAVG Rate          Total outlet number

Name 1     

 7016
Name 2      6789
Name 3      65120
Name 4      60110
Name 5      55200
Name 6      4690

 

The result should be name 2 because It is the one whose outlet number is greater than 50 and has the highest rate.

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!

2 Replies

  • 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!