Calculate average by distinct dividend value for each category in POWER BI / DAX

[SEPA]

Hi All,

I have a data like below and i want to calculate the average by distinct divident category.

Data:

Month	Country	Store	THRS	Value	%pctn
APR	Singapore	SG1	200	50	25.00
APR	Singapore	SG1	200	60	30.00
APR	Singapore	SG2	100	20	20.00

now i want to calculate the average by Store:

Expected result:

	Avg
SG1	27.50
SG2	20.00

The dax i tried:

measure1_mecaverage_by_str_monthly_trend =

CALCULATE (

AVERAGE ('Fact'[pctn]),

ALLEXCEPT ( 'Fact', 'Fact'[Month], Fact[Country],Fact[Store])

)

Can help to calculate average by using distinct divident value for each store

[v-cgao-msft · Accepted solution]

Hi @SEPA ,

Using your measures will give you the results you expect.

Or am I not understanding your needs? If you want to use different measure for different stores, you can try using the SWITCH functions. For example.

Measure = 
SWITCH(
    TRUE(),
    MAX('Fact'[Store]) = "SG1",[measure1_mecaverage_by_str_monthly_trend],
    MAX('Fact'[Store]) = "SG2",[measure2_mecaverage_by_str_monthly_trend])

Then drag the measure and field [Store] into the table visual.

The pbix file is attached for reference.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[v-cgao-msft · Accepted solution]

Hi @SEPA ,

Using your measures will give you the results you expect.

Or am I not understanding your needs? If you want to use different measure for different stores, you can try using the SWITCH functions. For example.

Measure = 
SWITCH(
    TRUE(),
    MAX('Fact'[Store]) = "SG1",[measure1_mecaverage_by_str_monthly_trend],
    MAX('Fact'[Store]) = "SG2",[measure2_mecaverage_by_str_monthly_trend])

Then drag the measure and field [Store] into the table visual.

The pbix file is attached for reference.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[SEPA]

Dear V-Cgao-msft,

Thanks for your response on this. And, yes the distinct average for store is comign fine, but however i am facing issue while calculating average for the calculated measure.

below is expected, 

month	country	store	thrs	values	Avergae of each record	Average of Store	Average of country
APR	Singapore	SG1	200	50	25.0%	27.50%	23.75%
APR	Singapore	SG1	200	60	30.0%
APR	Singapore	SG2	100	20	20.0%	20.00%
							this one is average of G

need to find out average of country from already calculated measure (average of store)

[SEPA]

yes i was looking the above output

[v-cgao-msft · Accepted solution]

Dear @SEPA,

Please try using this measure.

Average of country = 
CALCULATE (
    AVERAGEX (
        VALUES ( 'Fact'[Store] ),
        [measure1_mecaverage_by_str_monthly_trend]
    ),
    ALL ( 'Fact' )
)

The pbix file is attached for reference.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[SEPA]

Thank you, it works for me...