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 |
Tried to use an new created measure 'X' in which HOURS were summed up, and then tried to use 'X' in the bar chart as 'average of X'. The averages shown were not correct.
Try the following as a Measure:
Avg by Session =
AVERAGEX(
DISTINCT('YourTable'[SESSIONID]),
CALCULATE( SUM('YourTable'[HOURS]) )
)