topic Re: Average with diferent total in DAX Commands and Tips

Average with diferent total

Anonymous — Thu, 12 Aug 2021 17:26:45 GMT

Hi,

Can you help me? I have issues with this measure and I don´t know the reason.

name	1ª VISIT	2ª VISIT	Category	Total result
name1	02/06/2021 16:29	23/06/2021 14:50	3	82,8%
name2	02/06/2021 19:28	23/06/2021 13:36	3	48,0%
name3	02/06/2021 19:29	23/06/2021 16:36	3	92,4%
name4	02/06/2021 19:30		3	9,6%
name5	04/06/2021 6:50		3	28,8%
name6	04/06/2021 7:40		3	24,0%
name7	07/06/2021 10:15		3	12,0%
name8	07/06/2021 10:53		1	3,6%
name9	07/06/2021 11:32		1	15,2%
name10	07/06/2021 11:45		1	5,6%
name11	07/06/2021 12:00		1	8,4%
name12	07/06/2021 12:25		1	8,8%
name13	07/06/2021 13:12		1	4,0%
name14	08/06/2021 10:41		3	16,8%
name15	08/06/2021 11:59		2	11,2%
name16	08/06/2021 12:15		3	14,4%
name17	08/06/2021 12:40		3	18,0%
name18	08/06/2021 15:07		2	8,8%
name19	08/06/2021 15:59		2	14,4%
name20	09/06/2021 10:04		3	6,0%
name21	09/06/2021 10:56		1	9,2%
name22	09/06/2021 11:36		2	8,8%
name23	09/06/2021 12:05		2	9,6%
name24	09/06/2021 17:27		2	9,6%
name25	10/06/2021 11:00		3	30,0%

The total result: has the result of the inspections.

category: is a yardstick with the importance of installation

visit: are the dates of visit and can be once or twice

name: is the name of the facility.

And we need a measure. Average =the average of the visit but the total is the sum of the category average / category

Thanks a lot in advance

Re: Average with diferent total

Anonymous — Fri, 13 Aug 2021 11:28:42 GMT

"And we need a measure. Average =the average of the visit but the total is the sum of the category average / category"

About as clear as mud. Can you explain clearly what you need?

Re: Average with diferent total

Anonymous — Fri, 13 Aug 2021 12:35:24 GMT

the average of the "category total result" and the total is the average of the "category total result" divide by category

Re: Average with diferent total

Anonymous — Sun, 15 Aug 2021 21:16:47 GMT

Sorry. Same clarity as before.

Re: Average with diferent total

Jos_Woolley — Mon, 16 Aug 2021 06:01:58 GMT

Hi,

You mean this?

Measure = IF ( HASONEVALUE ( 'Table'[Category] ), AVERAGE ( 'Table'[Total result] ), DIVIDE ( SUM ( 'Table'[Total result] ), SUM ( 'Table'[Category] ) ) )

Regards

Re: Average with diferent total

Anonymous — Tue, 17 Aug 2021 18:44:19 GMT

Thank you, but not, the first part is OK

IF (
    HASONEVALUE ( 'Table'[Category] ),
    AVERAGE ( 'Table'[Total result] ),

The problem is with second.

I need also the sum average.

Now I am using

Total R = IF(HASONEVALUE(Table'[Name], AVERAGE( 'Table'[Total result] ), SUMX( Table, AVERAGE ( 'Table'[Total result] ) / SUM(Table[Category])).

I don´t Know Why?

But, Only it´s working if I filter "1 visit" by: Advanced filtering/ is no blank.

And I don´t know why I need that filter. I don´t have any blank and only it´s working If I do the filter on "1 visit".

I have other table "Regions" and the masure it´s working ( with the filter is no Blank) in all Regions except two

Re: Average with diferent total

Anonymous — Wed, 18 Aug 2021 08:15:35 GMT

Sorry, I try again

Now, I am using

Total R = IF(HASONEVALUE(Table'[Name], AVERAGE( 'Table'[Total result] ), SUMX( Table, AVERAGE ( 'Table'[Total result] ) / SUM(Table[Category])).

the average of the result: If "The Result" is 4 and there are two visit. the average is 2.

That for each row.

But the total result I need the sum of the average, divide total category

Re: Average with diferent total

Anonymous — Wed, 18 Aug 2021 17:44:10 GMT

https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/td-p/1626726

Re: Average with diferent total

Anonymous — Thu, 19 Aug 2021 08:14:33 GMT

Thank you and I´m sorry.

Closed this case, and I try again to explain it better