Pivot Table replication in PowerBI

[shreep1]

Hello Team,

@Amit, @Greg , @tamerj1 , @lbendlin,

@amitchandak , @olgad , @Sahir_Maharaj , @FreemanZ , @tamerj1 , @Greg_Deckler 

@christinepayton @audreygerred 

@LukeB,

@Kedar_Pande 

@rajendraongole1 

@Ritaf1983 

@SamWiseOwl 

I need to replicate the Pivot Table in Excel in my PowerBI. Pivot Table looks like this:

My current dataset looks like this:

I want to write an algorithm where it gives the summation of Avg Planned Cyc Time for each of the ESN Prefix for their corresponding Station No and it should reflect the Grand Total value of 764 towards the end counting the sum of all the Avg Planned Cyc Time for the list of Station No. EAC Serial are under the ESN Prefix.  

My Avg Planned Cyc Time column inside this data table is a Measure inside the Measures Table based on T_Cycle_Takt_Time_Detail data table where it is:

Avg Planned Cyc Time = AVERAGE(T_Cycle_Takt_Time_Detail[Planned Time [min]]])

I have written following algorithms but none of them are working for all the Prefixes when I put them on a slicer(filtering box) inside the PowerBI dashboard.

Grand Total Prefix 4 = SUMX('T_Cycle_Takt_Time_Detail', 'T_Cycle_Takt_Time_Detail'[Planned Time [min]]])

Grand Total Prefix 8 = SUMX(VALUES('T_Cycle_Takt_Time_Detail'), [Avg Planned Cyc Time])

Grand Total Prefix 9 = SUMX(SUMMARIZE('T_Cycle_Takt_Time_Detail','T_Cycle_Takt_Time_Detail'[Prefix],"AvgTime",[Avg Planned Cyc Time]),[AvgTime])

Grand Total 10 = VAR CurrentESNPrefix = SELECTEDVALUE('T_Cycle_Takt_Time_Detail'[Prefix]) RETURN SUMX(FILTER('T_Cycle_Takt_Time_Detail', 'T_Cycle_Takt_Time_Detail'[Prefix] = CurrentESNPrefix), 'MEASURE'[Avg Planned Cyc Time])

My best algorithm is Grand Total Prefix 4 or Grand Total Prefix 8  where it works and correctly gives the correct Grand Total for most of the Prefixes but very incorrect results for some of the Prefixes due to which I cannot use it.

Can you please suggest a better algorithm? Why is this algorithm not working. Is it because of filter context or the PowerBI dashboard being too old or something else.

I will appreciate more than any help.

Thanks,

[shreep1]

Hello @Ashish_Mathur ,

I cannot share the PBI file. My expected result is that it correctly shows the correct Grand Total(summation of Avg Planned Cyc Time for all the stations belonging to that Prefix) when I use the prefix slicer. Its working for most of the Prefixes but not for all. 

Thanks

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the expected result very clearly.