cancel
Showing results for
Did you mean:
Member

## Hiding the empty rows in a running total

Hi all

I have a running total being calculated, but it is returning blank values where there is no data for the filters applied.

Hours table:

 Date Employee Hrs Job 20/12/2018 A 18 2000 25/12/2018 B 5 1000 29/12/2018 B 9 1000 01/01/2019 A 5 1000 02/01/2019 B 2 1000 03/01/2019 B 4 2000 04/01/2019 B 5 1000 05/01/2019 A 1 1000 06/01/2019 A 4 1000 07/01/2019 A 5 1000 08/01/2019 A 7 1000 09/01/2019 A 1 1000 10/01/2019 A 1 1000 11/01/2019 A 8 1000 12/01/2019 B 4 1000 13/01/2019 A 8 1000 14/01/2019 A 12 1000 15/01/2019 A 16 1000

FHours table:

 Date Hrs Job 01/11/2018 50 1000 02/12/2018 150 2000 06/01/2019 5 1000 08/03/2019 18 1000

Matrix table:

 Job Lead 1000 A 2000 B

The relationships are:

Hours (Job)  MANY*---1ONE  Matrix (Job)

Matrix (Job) ONE1---MANY* FHours (Job)

I have two measures:

```HrsActualR =

CALCULATE(
SUM('Hours'[Hrs]),
FILTER(
ALLSELECTED('Hours') , 'Hours'[Date]<=max(Hours[Date]) && Hours[Employee]=distinct('Matrix'[Lead]) )
)```
```HrsRunningF =

CALCULATE(
SUM('FHours'[Hrs]) )```

I have a slicer to select the Job from Matrix table. I want to show the below in a table visual:

 Date HrsActualR HrsRunningF 01/01/2019 5 73 05/01/2019 6 73 06/01/2019 10 73 07/01/2019 15 73 08/01/2019 22 73 09/01/2019 23 73 10/01/2019 24 73 11/01/2019 32 73 13/01/2019 40 73 14/01/2019 52 73 15/01/2019 68 73

However what I am getting is:

Is there any way to get the data I need, without the blank/duplicate entries?

See below the PBIX if this would help with this:

https://1drv.ms/u/s!AuiIgc_S9J5JhbYTFO35ar1ki8imjw

Many thanks for all help

5 REPLIES 5
Super User

## Re: Hiding the empty rows in a running total

if you add the top line  to your HrsRunningF measure it will only show when there is a value for HrsActualR

```HrsRunningF =
IF ( NOT( ISBLANK( [HrsActualR])),
CALCULATE(
SUM('FHours'[Hrs]) )
)```
Member

## Re: Hiding the empty rows in a running total

Hi @Nick_M thanks for your reply, I have amended the measure per your suggestion and it is nearly there, however, the duplicates on the HrsActualR measure are still present - any ideas how to resolve that?

This is what I have now:

But this is what I need:

 Date HrsActualR HrsRunningF 01/01/2019 5 73 05/01/2019 6 73 06/01/2019 10 73 07/01/2019 15 73 08/01/2019 22 73 09/01/2019 23 73 10/01/2019 24 73 11/01/2019 32 73 13/01/2019 40 73 14/01/2019 52 73 15/01/2019 68 73

Cheers

Highlighted
Super User

## Re: Hiding the empty rows in a running total

If you just want to avoid showing the blanks in [HrsActualR] you can create a measure like this, place it in visual level filters and select 'Show items when the value is' --> 1

```ShowMeasure =
IF ( NOT ISBLANK ( [HrsActualR] ); 1 )```

If you want to show only the first appearance of each pair  ([HrsActualR] , [HrsRunningF]) as you seem to imply in your expected result, you can create the following (rather complex) measure instead. Place it in visual level filters and select 'Show items when the value is' --> 1.  Most likely there are simpler ways to accomplish this by modifying your measures but well, it seems to do the job.

By the way, I've noticed that the set-up you have in the pbix throws an error when both 1000 and 2000 are selected in the slicer.

```ShowMeasure2 =
VAR _AuxTable =
FILTER (
CALCULATETABLE ( DISTINCT ( Hours[Date] ); ALL ( Hours[Date] ) );
"Meas1"; [HrsActualR];
"Meas2"; [HrsRunningF]
);
NOT ISBLANK ( [Meas1] )
)
VAR _ResTable =
FILTER (
_AuxTable;
Hours[Date]
= MINX (
_AuxTable;
IF (
[Meas1] = EARLIER ( [Meas1] ) && [Meas2] = EARLIER ( [Meas2] );
Hours[Date]
)
)
)
RETURN
IF (
CONTAINSROW (
_ResTable;
SELECTEDVALUE ( Hours[Date] );
[HrsActualR];
[HrsRunningF]
);
1
)```

Member

## Re: Hiding the empty rows in a running total

Hi @AlB thanks for your response. @Nick_M has suggested a solution for the blanks which works very well.

As for the duplicates, they are showing because on some days there are hours posted for the correct job but for a different Employee ID - my DAX is only looking for hours posted for the Matrix[Lead] employee ID for that job. However, the visual still shows the date - I guess the most elegant way would be to exclude dates where the hours posted are not for the required Employee ID - but not sure how to do that. I have tried your solution below, and it does work, just a bit worried it may exclude data which is needed? As I am not too sure what it is doing. But many thanks for this, I will use it and keep checking it to see if any issues arise

Any ideas on how I could fix the issue with multiple jobs being selected in the slicer? Didnt even see that, thanks for the spot @AlB

Many thanks!

Super User

## Re: Hiding the empty rows in a running total

I haven't understood completely what your measures are all about but try this plus the measure Nick suggested:

```HrsActualR_V2 =
IF (
NOT ISBLANK (
CALCULATE (
SUM ( 'Hours'[Hrs] );
Hours[Employee] IN DISTINCT ( 'Matrix'[Lead] )
)
);
CALCULATE (
SUM ( 'Hours'[Hrs] );
FILTER ( ALL ( 'Hours'[Date] ); 'Hours'[Date] <= MAX ( Hours[Date] ) );
Hours[Employee] IN DISTINCT ( 'Matrix'[Lead] )
)
)```

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,374)