Aggregated data and getting the consolidated information

[Davor_K]

Hi all,

this is my first time posting in the forum I hope that I will able to get a help with the issue that I have been trying to solve for a while.

I have the table with the following information:

Here I have the list of the different projects impacting different companies/entities and units. Also, I have assessment of their impacts on technology and processes. Based on those two impact values, I have calculated Total impact.

Now, I would like to aggregate/consolidate information, so I can see what would be average impact value and grading (high/medium/low) of the particular project on company/entity/unit selected through the slicers. For example, if I select Entity A I should get:

For that purpose, I created calculated table that should aggregated the Name of the projects value, calculate average of Total impact value and set the grading (high/medium/low):

CalculatedTable =

    ADDCOLUMNS(

        SUMMARIZE(Sheet1;

                Sheet1[Name of the project];

                "Average_impact"; AVERAGE(Sheet1[Total impact]));

                "Impact level"; if([Average_impact]<=1,5; "High";IF(AND([Average_impact]>=1,5;[Average_impact]<2,5);"Medium";"Low")))

Also, I have created Many-to-one, Both, relationship between data table (Sheet1) and calculated table (CalculatedTable) by connecting the Name of the project value. 

The issue that I am getting is no matter which combination of the slicers I select, the average value of the impact value is always the same. It seems that calculated table does not care about the selected values through the slicers when calculating the average impact, but it uses all information from the data table:

When selecting different slicer:

Here the average impact for Project C and Project A should be as same as in the data table (3,00 and 2,50).

Here the average impact for Project B and Project C should be as same as in the data table (1,00 and 1,50).

If somebody was experiencing the similar issue or know how to fix this, I would really appreciate some guidance.

Here is the .pbix: https://www.dropbox.com/s/b2a8nsyks8b7p2w/Project_test_PBI1.pbix?dl=0

Many thanks, best regards.

[ChandeepChhabra]

@Davor_K Please download the Power BI file

[Davor_K]

@ChandeepChhabra works good, I have already implemented this solution. The question is how to get Impact Tag as a legend in a gantt vizual 

To do that, Impact Tag should be a column value instead of measure as far as I have managed to find until now. 

Thank you and best regards.

[ChandeepChhabra]

@Davor_K 

In the existing Power BI file. Please add a new column with the following formula

Column = [Impact Tag]

Thanks

[Davor_K]

@ChandeepChhabra that is good, and results with the following overview:

It would be good if there would be unique values in the project's name. What is needed is consolidated information, meaning something like:

To consolidate, for example, project names I used calculated table to get "Project A", "Project B" and "Project C". That is ok, but the impact level is calculated based on all fields and it is not re-recalculated by using the slicers.

Thank you and best regards.

[ChandeepChhabra]

@Davor_K Can you please share your pbi file which has this chart?

[Davor_K]

@ChandeepChhabra here you go:

https://www.dropbox.com/s/87ut0dz6b5kbpo4/Project_test_PBI2.pbix?dl=0

Best regards.

[parry2k]

@Davor_K you dont need to create caculated table to achieve this.

Add following measures

Avg Impact = AVERAGE( Sheet1[Total impact] )

Impact level = 
SWITCH( TRUE(),
    [Avg Impact]<=1.5, "High",
    AND([Avg Impact]>=1.5,[Avg Impact]<2.5),"Medium",
    "Low"
)

Now in your table visuals uses these measure and name of the project from your main table. 

[Davor_K]

Hi parry2k,

Many thanks on feedback. The solution works great, however I need this information within calculated table because later on I am going to use it as a legend in the gantt chart. I tried to apply same solution as calculated table, but it does not work...it keeps the constant values again.

Best regards.

[parry2k]

@Davor_K once you have calculated table, slicer will not work because values are pre calculated.

[Davor_K]

@parry2k I was trying to find any solution how to consolidate information to have an aggregated overview and the only way that I was able to find is to create calculated table, which would be used as basis for other visuals. But if calculated table is not dynamic, any other ideas to look at?

Thank you and best regards.

[parry2k]

@Davor_K what is the limitation of measures I proposed originally

[Davor_K]

@parry2k The limitation is that I cannot use measure as a legend in gantt chart, but instead I have to use column values.

For example:

I selected Entity B and got two projects in the gantt which impacts that Entity: Project A and Project C. So far, so good.

Now, I would like to visualize the level of impact by using the legend feature in the gantt. If I drag measure, the gantt does not accept it, bacause I need to use the column values (in this case, Impact level (column value)) which shows wrong information. To get the bar properly colored, I need to use the values from your solution, but I cannot find the way how to do it.

Thank you and best regards.