Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Iryna Woni]

Hello everyone,

I hope you can help me…

E.g. I’ve got such data:

Country	Department	Amount
Denmark	1111DK	101020
Denmark	2222DK	101021
Denmark	3333DK	101022
Denmark	1111DK	101025
Denmark	2222DK	101026
Denmark	3333DK	101027
Denmark	1111DK	101030
Denmark	2222DK	101031
Denmark	3333DK	101032
Denmark	1111DK	101035
Denmark	2222DK	101036
Denmark	3333DK	101037
Denmark	1111DK	101040
Denmark	2222DK	101041
Denmark	3333DK	101042
Denmark	1111DK	101045
Denmark	2222DK	101046
Denmark	3333DK	101047
Norway	1111NO	101048
Norway	2222NO	101049
Norway	3333NO	101050
Norway	1111NO	101051
Norway	2222NO	101052
etc.

I need to calculate percentile/average amount for a country per department and then average from those percentiles/averages. How to do it in Power BI? 

In the report I would like to get such table:

Country	Department weighted Average (amount)	Department weighted Percentile (amount)
Denmark	AVERAGE(AVERAGE(Amounts for 1111DK), … , AVERAGE(Amounts for 3333DK))	AVERAGE(PERCENTILE(Amounts for 1111DK), … , PERCENTILE(Amounts for 3333DK))
Norway	etc.	etc.

The second question (I hope it is not too much…) : the departments can consist of sub-departments and I may need to calculate those measures by departments AND sub-departments.

Will be very grateful for your help!

——————————
Thank you in advance,
Iryna
——————————

[Greg Philps]

Hi Iryna. You can use the CALCULATE and ALLEXCEPT DAX functions to get sales by department and sales by country, then DIVIDE to calculate the percentages. I’m not exactly sure of what you were looking for, but hopefully this sample will give you a few ideas. Greg

——————————
Greg Philps
Power BI Consultant
Ottawa ON Canada
——————————
——————————————-