Median, Mode and Max

[Purushothaman]

Hi Experts,

Attached is my QVF. Below is the Pivot table from the QVF. 

The Median, Mode and Max  columns are not changing dynamically based on selection of Year and MemberID.  

Anyone, Please help!!

Thank you!!

[WildmoserGeorg · Accepted solution]

This is due to the data model.  The values must also be determined for the years. You can either include the years in the key in the data model or determine them in the diagram as a measure.

Here is an example if you want to solve it via the script. You need each demension to display in the key (just included the meetings in the example).

The script (data editor):

Raw:

Load *,Meeting&Year as KEYYearMeeting
;

Load * inline [

Year|MemberID|Meeting
2021|M01|Online
2021|M01|Phyiscal
2021|M01|Online
2021|M01|Online
2021|M01|Online
2021|M02|Phyiscal
2021|M02|Phyiscal
2021|M02|Phyiscal
2021|M02|Online
2021|M02|Online
2021|M02|Online
2021|M02|Online
2021|M03|Phyiscal
2021|M03|Phyiscal
2021|M03|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M02|Phyiscal
2022|M02|Online
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M02|Online
2022|M02|Online
2022|M02|Online

2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M02|Phyiscal
2023|M02|Online
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M02|Online
2023|M02|Online
2023|M02|Online
2023|M02|Phyiscal]

(delimiter is '|');

Raw1:
Load *,
Meeting as M
Resident Raw;
Drop Table Raw;

Output_1:

Load
Year,
M as MT,

Count(M)/NoOfRows('Raw1')* 100 as Per%

Resident Raw1

Group by Year,M;

Output_Mode:
Load MT&Year as KEYYearMeeting,
ModePer%
;

Load
MT,
Year,
Mode(Per%) as ModePer%
Resident Output_1
Group by MT, Year
;

Output_Median:

Load MT&Year as KEYYearMeeting,
MedianPer%
;

Load
MT,
Year,
Median(Per%) as MedianPer%
Resident Output_1
Group by MT, Year
;

Output_Max:
Load MT&Year as KEYYearMeeting,
MaxPer%
;

Load
MT,
Year,
max(Per%) as MaxPer%
Resident Output_1
Group by MT, Year
;

Drop Table Output_1;

The measures :

=sum([ModePer%])

=sum([MedianPer%])

=max([MaxPer%])

[WildmoserGeorg · Accepted solution]

By Formula:

=Mode(aggr(Count(Meeting)/Count(Total Meeting),Meeting,Year))

=median(aggr(Count(Meeting)/Count(Total Meeting),Meeting,Year))

=max(aggr(Count(Meeting)/Count(Total Meeting),Meeting,Year))

Script:

Raw:

// Load *,Meeting&Year as KEYYearMeeting
// ;

Load * inline [

Year|MemberID|Meeting
2021|M01|Online
2021|M01|Phyiscal
2021|M01|Online
2021|M01|Online
2021|M01|Online
2021|M02|Phyiscal
2021|M02|Phyiscal
2021|M02|Phyiscal
2021|M02|Online
2021|M02|Online
2021|M02|Online
2021|M02|Online
2021|M03|Phyiscal
2021|M03|Phyiscal
2021|M03|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M02|Phyiscal
2022|M02|Online
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M02|Online
2022|M02|Online
2022|M02|Online

2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M02|Phyiscal
2023|M02|Online
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M02|Online
2023|M02|Online
2023|M02|Online
2023|M02|Phyiscal]

(delimiter is '|');

[Purushothaman]

Dear @WildmoserGeorg ,

Thanks a lot for providing  super two solutions instantly. Much Appreciated!!