Show the details of a calculation

[bebeto]

Hello,

I have a measure with the active employees in my company. What I want to get is who are those employees in a a table, excel or similar. Something like a detail report (Name, Address, etc...).

Is this possible in Power BI?

Thanks in advance!

[v-haibl-msft]

@bebeto

I assume you’re still working around the dataset of your previous topic Cumulative measure in past years and months. You can drag a Table chart into your canvas and select the values you want to display along with the active employees measure.

Best Regards,

Herbert

[bebeto]

Yes, that is the idea. The problem is when the chart is not selected, then the table shows data and this could be confusing to the user. Could see the table only when the chart is selected?

[v-haibl-msft]

@bebeto

I can only come up with a trick as below.

Drag a slicer into your canvas (select any column you wanted, I use resource_typ here). Edit interactions of it to make it can only filter the table chart.

When the column chart is not selected, select (Blank) and table chart will show nothing.

Before you want to select one column in the column chart, unselect (Blank) and then select in the column chart.

Best Regards,

Herbert

[bebeto]

@v-haibl-msft

I have some problem with the interaction between a bart chart and its table to show the details.

The situation is:

- Bar Chart showing Incorporation by year. The calculation is:

IncorporacionesPorAños =
IF
(
MIN (DimFecha[Date]) <= TODAY ();
CALCULATE
(
COUNTROWS(Empleados);
FILTER(Empleados;Empleados[Año_incorporacion] <= YEAR(MIN(DimFecha[Date])) && Empleados[Año_incorporacion] = YEAR(MIN(DimFecha[Date])))
);
BLANK()
)

- Second bar chart showing the incoporation by month based on the first year selected. The calculation is:

IncorporacionesPorMeses =
IF
(
MIN (DimFecha[Date]) <= TODAY ();
CALCULATE
(
COUNTROWS(Empleados);
FILTER(Empleados;Empleados[Mes_incorporacion] = MONTH(MIN(DimFecha[Date])) && Empleados[Año_incorporacion] =YEAR(MAX(DimFecha[Date])))
);
BLANK()
)

- And a table to show the employees of this second month basr chart selected.

Here is the problem, when I select the value of a month, nothing is showed in the third table, the bar chart from which I select the month changes and (Blank) is showed and the first bar chart looses the value previous selected.

Any ideas about what could be happening?

BEFORE SELECT THE MONTH IN SECOND BAR CHART:

AFTER SELECT THE MONTH IN SECOND BAR CHART:

[v-haibl-msft]

@bebeto

Please try to change the Month in second bar chart to YearMonth. In your above chart, if you select Month from second chart, the first bar chart will lose the value selected because this month exist in all years. Please see following simple chart sample.

Best Regards,

Herbert

[bebeto]

The problem is not solved...

When I select the month in the second chart, both charts looses the value selected. ¿Could  be the problem in the metrics showed in the charts?

The first is:

IncorporacionesPorAños =
IF
(
MIN (DimFecha[Date]) <= TODAY ();
CALCULATE
(
COUNTROWS(Empleados);
FILTER(Empleados;Empleados[Año_incorporacion] <= YEAR(MIN(DimFecha[Date])) && Empleados[Año_incorporacion] = YEAR(MIN(DimFecha[Date])))
);
BLANK()
)

And the second is:

IncorporacionesPorMeses =
IF
(
MIN (DimFecha[Date]) <= TODAY ();
CALCULATE
(
COUNTROWS(Empleados);
FILTER(Empleados;Empleados[Mes_incorporacion] = MONTH(MIN(DimFecha[Date])) && Empleados[Año_incorporacion] =YEAR(MAX(DimFecha[Date])))
);
BLANK()
)

[v-haibl-msft]

@bebeto

Have you edited the interactions as below?

Best Regards,

Herbert

[bebeto]

Yes...

And when I select a year-month in the second chart (201608 for example) the result is:

[v-haibl-msft]

@bebeto

If possible, could you please upload your PBIX file and share with me for troubleshooting? You can remove the sensitive information from your file.

Best Regards,

Herbert

[bebeto]

Thanks for thr trick @v-haibl-msft

I don't know if it is a good solution for my customer, I will try!

Thanks!

[dpotta]

You could use the Table Visualisation and add the (Name, Address, etc...) fields as columns in the table.

[bebeto]

Sorry, my explanation wasn't complete. I want to show this data when I click in a bar chart, for example...

What I want is to get the data of these 19 employees. thanks!

[dpotta]

@bebeto Oh Okay!

Yep: on the axis add (for example) the Office as the top level and the Employees (Name in the screen grab) in that office as the next level down in the hierachy.

i.e. drag Office onto the axis and then Employee below it. If they aren't from the same table you will need to create a Relationship between tables.

[dpotta]

If you wanted more info: you would use the bar-chart as a 'filter' and add a table underneath it as a separate visualisation. This could then display address etc...