cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ansa_naz Member
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.

 

Please see example data below:

 

Hours table:

 

DateEmployeeHrsJob
20/12/2018A182000
25/12/2018B51000
29/12/2018B91000
01/01/2019A51000
02/01/2019B21000
03/01/2019B42000
04/01/2019B51000
05/01/2019A11000
06/01/2019A41000
07/01/2019A51000
08/01/2019A71000
09/01/2019A11000
10/01/2019A11000
11/01/2019A81000
12/01/2019B41000
13/01/2019A81000
14/01/2019A121000
15/01/2019A161000

 

FHours table:

 

DateHrsJob
01/11/2018501000
02/12/20181502000
06/01/201951000
08/03/2019181000

 

Matrix table:

 

JobLead
1000A
2000B

 

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:

 

DateHrsActualRHrsRunningF
01/01/2019573
05/01/2019673
06/01/20191073
07/01/20191573
08/01/20192273
09/01/20192373
10/01/20192473
11/01/20193273
13/01/20194073
14/01/20195273
15/01/20196873

 

 

However what I am getting is:

 

RunningTotals.jpg

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
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]) )
)
ansa_naz Member
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:

 

RunningTotals1.jpg

But this is what I need:

 

DateHrsActualRHrsRunningF
01/01/2019573
05/01/2019673
06/01/20191073
07/01/20191573
08/01/20192273
09/01/20192373
10/01/20192473
11/01/20193273
13/01/20194073
14/01/20195273
15/01/20196873

 

 

Cheers

Highlighted
Super User
Super User

Re: Hiding the empty rows in a running total

Hi @ansa_naz 

Note this is using your measures as defined in your pbix

 

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 (
        ADDCOLUMNS (
            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
    )

 

 

image.png

 

 

ansa_naz Member
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
Super User

Re: Hiding the empty rows in a running total

@ansa_naz 

 

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] )
    )
)

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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