average of summed values, in a visual

[RonaldR]

Hi experts,

Just started using PBI Desktop.

Could anyone help me ?

Thank you in advance!

Problem description/what I want to achieve:

Based on source data, I want to make a visual (grouped bar chart) in which the Y-axis represents average values (HOURS) of previously summed up rows (SESSIONID), per X-axis value (ZVTCODE). So, there needs to be summed up first, then the averages need to be calculated and finally those averages need to be shown as bars in the chart.

The data are:

source data

ZVTCODE	SESSIONID	HOURS
ZT_01	10	1
ZT_01	10	1
ZT_01	10	2
ZT_01	10	1
ZT_01	10	4
ZT_01	11	2
ZT_01	11	3
ZT_01	11	1
ZT_01	12	5
ZT_01	12	1
ZT_01	12	2
ZT_01	12	3
ZT_01	12	3
ZT_02	13	2
ZT_02	13	3
ZT_02	14	1
ZT_02	14	4
ZT_02	14	2
ZT_02	14	1
ZT_02	15	2
ZT_02	15	1
ZT_02	16	2
ZT_02	16	4
ZT_02	16	3

The hours need to be summed up per SESSIONID

SESSIONID:ZVTCODE = n:1

SESSIONID is unique (i.e. is a primary key)

Note: this table/data will not be used, it is just shown here for explanation.

ZVTCODE	SESSIONID	HOURS_TOTAL
ZT_01	10	9
ZT_01	11	6
ZT_01	12	14
ZT_02	13	5
ZT_02	14	8
ZT_02	15	3
ZT_02	16	9

Target data should be like this:

average hours, per ZVTCODE

Finally, the data needs to be used in a visual (grouped bar chart).

ZVTCODE	HOURS_AVG
ZT_01	9,67
ZT_02	6,25

[RonaldR]

Hi danextian, it works!

Thank you very much for your help!

[danextian · Accepted solution]

Hi @RonaldR 

Please try this:

avg = 
VAR __TBL =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[ZVTCODE], Data[SESSIONID] ),
        "@hours", CALCULATE ( SUM ( Data[HOURS] ) )
    )
RETURN
    DIVIDE ( SUMX ( __TBL, [@hours] ), COUNTROWS ( __TBL ) )