Sum & average

[abukapsoun]

 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. 

Date	Flag	City	People
11nov	msc1	ger	6
11nov	msc1	ger	2
11nov	msc1	it	89
11nov	msc1	it	12
11nov	msc2	ger	12
11nov	msc2	ger	15
12nov	msc2	it	155

[Anonymous]

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.

 

[abukapsoun]

Didn't really work with me 😞 

Can you please check the following post, I have included the dataset I am working on. I would really appreciate if you can have a look please.

https://community.powerbi.com/t5/Desktop/Matrix-with-different-level/m-p/2902152#M997246

Thanks,

[Ashish_Mathur]

Hi,

This measure works

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

Hope this helps.

[abukapsoun]

Hi @Ashish_Mathur 

What is the "Total" measure you have created? I can see it in your screenshot but not in the formula

Thank you

[Ashish_Mathur · Accepted solution]

Download the PBI file.

[Greg_Deckler]

@abukapsoun Try:

Measure = AVERAGEX(SUMMARIZE('Table',[Date],[City],"__Sum",SUM('Table'[People]),[__Sum])

[abukapsoun]

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?