Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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 ) )
Proud to be a Super User!
Hi danextian, it works!
Thank you very much for your help!
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 ) )
Proud to be a Super User!
User | Count |
---|---|
99 | |
86 | |
80 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |