Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
RonaldR
New Member

average of summed values, in a visual

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

ZVTCODESESSIONIDHOURS
ZT_01101
ZT_01101
ZT_01102
ZT_01101
ZT_01104
ZT_01112
ZT_01113
ZT_01111
ZT_01125
ZT_01121
ZT_01122
ZT_01123
ZT_01123
ZT_02132
ZT_02133
ZT_02141
ZT_02144
ZT_02142
ZT_02141
ZT_02152
ZT_02151
ZT_02162
ZT_02164
ZT_02163

 

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.

ZVTCODESESSIONIDHOURS_TOTAL
ZT_01109
ZT_01116
ZT_011214
ZT_02135
ZT_02148
ZT_02153
ZT_02169

 

Target data should be like this:

average hours, per ZVTCODE

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

ZVTCODEHOURS_AVG
ZT_019,67
ZT_026,25
1 ACCEPTED SOLUTION
danextian
Super User
Super User

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 ) )

danextian_0-1707779701235.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

2 REPLIES 2
RonaldR
New Member

Hi danextian, it works!

Thank you very much for your help!

danextian
Super User
Super User

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 ) )

danextian_0-1707779701235.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.