0

Working in Excel (Microsoft Office LTSC Professional Plus 2021)

I am trying to plot some measures using a pivot chart object but as far as I could investigate, it seems what I am trying to do it is not working.

I will explain a simplified scenario just only to show up the problem.

I am having some raw data of some indicators. So in detail it is a table with data of some industrial indicators (15 indicators) of last 3 months of productive plants (15 productive plants). This data it is something inside the workbook as a data range in one of the tabs.

I feed workbook data model with this data using power query. So I make some transformations of this table in power query environment in order to have better data structure, etc, et. In this stage, I also add some additional data dimension that I need. In detail, a Calendar dimension, a Productive Plant Dimension, KPI Dimension...

After that, In power pivot environment, I build the model. I make the relationships between tables and I create the measures.

So regarding the measures (and in order to make it simple) what I define it is the average of X. So we are having 15 averages. For instance "Average of OEE", "Average of TI", "Average of Downtime", etc

The requirement it is some front where there is just only 1 pivot table and then depending on a selection made by the user dynamically by means of an slicer (KPI Selection) then information of one indicator or another then it is shown in this pivot.

By this reason I created also in power pivot some "consolidation measures" using DAX. So depending on selection made with slicers about desired indicator then this measure it is fed with specific indicator average measure or another.

*code sniped

Indicator_Value_1:=SWITCH(TRUE(), IF(HASONEVALUE(Dim_KPIs_1[KPI - 1]), VALUES(Dim_KPIs_1[KPI - 1])) = "OEE", CALCULATE([Avg_OEE],FILTER(IN_INJECTION,NOT ISBLANK(IN_INJECTION[Average of OEE]))), IF(HASONEVALUE(Dim_KPIs_1[KPI - 1]), VALUES(Dim_KPIs_1[KPI - 1])) = "TI", CALCULATE([Avg_TI],FILTER(IN_INJECTION,NOT ISBLANK(IN_INJECTION[Average of TI]))), IF(HASONEVALUE(Dim_KPIs_1[KPI - 1]), VALUES(Dim_KPIs_1[KPI - 1])) = "TI YTD", CALCULATE([Avg_TI YTD],FILTER(IN_INJECTION,NOT ISBLANK(IN_INJECTION[Average of TI YTD]))), IF(HASONEVALUE(Dim_KPIs_1[KPI - 1]), VALUES(Dim_KPIs_1[KPI - 1])) = "Av", CALCULATE([Avg_Av],FILTER(IN_INJECTION,NOT ISBLANK(IN_INJECTION[Average of Av]))), IF(HASONEVALUE(Dim_KPIs_1[KPI - 1]), VALUES(Dim_KPIs_1[KPI - 1])) = "L1_205 MACHINE DT", CALCULATE([Avg_L1_205 MACHINE DT],FILTER(IN_INJECTION,NOT ISBLANK(IN_INJECTION[Average of L1_205 MACHINE DT]))), IF(HASONEVALUE(Dim_KPIs_1[KPI - 1]), VALUES(Dim_KPIs_1[KPI - 1])) = "L1_210 IN SITU MOLD FIX UP", CALCULATE([Avg_L1_210 IN SITU MOLD FIX UP],FILTER(IN_INJECTION,NOT ISBLANK(IN_INJECTION[Average of L1_210 IN SITU MOLD FIX UP]))),BLANK()) *

Finally, I create a pivot table where I place in measures section this "consolidation measure" and it works. So depending on the KPI selected by means of the slicer then the according measure value it is shown in the pivot.

But the problem is that when I try to do the same but with a pivot chart it doesn't works. So in this case the chart it is empty even in the equivalent pivot table I see data...

I tried to create a pivot chart using this measure and it doesn't works. I tried also creating some data range as an offset of this pivot table data and then define this new data range as source for this data chart and neither works.

I am thinking on use VBA in order to manage the dynamic creation of charts and pivots depending on selection made with slicers but I would not do it because I consider I am losing DAX power...So we have a powerful language that it is DAX but we need to keep using VBA in order to manage this situation ?

I there any lack of functionality on pivot charts related with the ability of manage dynamic measures created with power pivot ?

Improve this question
3
  • A pivot chart should always reflect the same data as the pivot table it is based on. Any chance you can put a sample workbook somewhere for us to look at? Commented Jan 31 at 15:15
  • So the context is to have a tool in order to provide ability for compare 2 indicators dynamically depending on users selection. The user can select the pull of plants involved in the analysis as well as the range of dates considered for the analysis. Automatic Best in Class detected dynamically for each indicator selected and a manual Plant reference assignation also available for creating measures comparing self Plant values with those references. In this tool the idea is to have both pivots as I already have and the next step was to add their related pivots charts but...I am stuck with that. Commented Jan 31 at 18:07
  • Finally, I discovered the root cause of that behavior. The problem was related with consolidating a measure value with some format applied (i my case a "0.00%" format). When I do that, In terms of pivot table, there are no problems so pivot table shows the measure with the applied format but when I try to graph the same measure in a pivot chart then it appears empty. In order to solve this, I removed this format on the measure layer and then pivot chart it is not empty so it works. In this case, I consider this topic as closed. Commented Feb 2 at 6:08
Related questions
0
Date filtering of DAX measure in pivot chart
0
DAX calculated measure in Power Pivot 2016
0
Calculating a weighted average of existing measures in Power Pivot
Load 3 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.