topic Re: Average of a percentage in a Matrix in DAX Commands and Tips

Average of a percentage in a Matrix

JB_AT — Wed, 26 Feb 2025 06:49:38 GMT

Good morning

I have a matrix visual and it has 11 Rows(Hierarcies) Company from the DIM_Company table and Hierarchy 1 to 10 from the DIM_Employee Table. They have a relationship with the Hours table.

My measure is a percentage of two measures from the Hours table DIVIDE( {Measur1, Measure2)

When I add this measure, I would like to get the Average % of the Hierarchy.
Example: Company is at the top of the hierarchy and shouls show the average of all records in all hierarchies. Hierarchy1 should show the the same but for all below it. And so on

I have tried to follow GregDeckler s instructions here https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/m-p/411443 , but I can't replicate.

Any suggestions would be helpful

Thanks

Re: Average of a percentage in a Matrix

Rupak_bi — Wed, 26 Feb 2025 07:16:15 GMT

please share sample data and desired output.

Re: Average of a percentage in a Matrix

JB_AT — Wed, 26 Feb 2025 07:37:50 GMT

Hi Rupak_bi

Here is the desired output

Hierarchy 1	Hierarchy 2	Hierarchy 3	Amount
Company1			64%
	Name1		57.50%
		Name	50%
		Name	30%
		Name	70%
		Name	80%
	Name2		70%
		Name	60%
		Name	70%
		Name	70%
		Name	80%

I can't provide data. But Compayn1 come from DIM_Company and Names come from DIM_Employee and both dimensions have a 1 to n relationship with the hours table.

The percentage Amount measure is calculated from 2 measures coming from the hours table.

Thanks

Re: Average of a percentage in a Matrix

Rupak_bi — Thu, 27 Feb 2025 12:59:13 GMT

Hi JB_AT ,

Without sample data and the data model overview, it is hard to provide desired solution. Moreover I also like to know what kind of output you are currently getting with your existing calculations. However, If you refer a measure which is dividing two measures, will not give an average at upper hierarchies. Top get that, the simplest way is to avoid measure and do the divide in a calculated column and refer that. Else, the sample data and the repationship details is really need to understand.

thanks.