Forum Discussion

abukapsoun's avatar
abukapsoun
Post Patron
3 years ago
Solved

Sum & average

 Hi Guys,

 

Need your support please,

 

I have the following dataset

 

for each of the day: I want to Sum people based on city, then i want to average the city based on flag. 

so Ger = average (msc1 (6+2) +msc2 (12+15))

appreciate if you can help me with the right measure to use. 

DateFlagCityPeople
11novmsc1ger6
11novmsc1ger2
11novmsc1it89
11novmsc1it12
11novmsc2ger12
11novmsc2ger15
12novmsc2it155
    
Need Help

7 Replies

    • abukapsoun's avatar
      abukapsoun
      Post Patron
      3 years ago

      Hi Greg,

       

      It didn't work, it seems there is a syntax error

      I am getting the following: 

      Too few arguments were passed to the AVERAGEX function. The minimum argument count for the function is 2.

      As well I got error under: 

      [__Sum]

      Can you maybe send me a pbix for it? 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    This measure works

    Measure = AVERAGEX(VALUES(Data[Flag]),[Total])

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi abukapsoun ,

    Please try below steps:

    1. below is my test table

    Table:

    Table2:

    create with below dax formula:

    Table 2 = VALUES('Table'[City])

     

    2. create a measure with below dax formula

    Measure =
VAR cur_city =
    SELECTEDVALUE ( 'Table 2'[City] )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), [City] = cur_city )
VAR tmp2 =
    SUMMARIZE (
        tmp1,
        'Table'[Date],
        'Table'[City],
        'Table'[Flag],
        "Sum", SUM ( 'Table'[People] )
    )
RETURN
    AVERAGEX ( tmp2, [Sum] )

    3. add a slicer with Table2, add a card visual with above measure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.