Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
toniacheung
Frequent Visitor

help with drill through with cumulative total

 

I have a visual that shows the number of incidents over the year, with a line showing the accumulation for the year.  When I drill through to show the data by week (or month, or year), however, the line showing the cumulation doesn't work right.  It just shows the total for each time period rather than for the whole year.   How can I still get it to show the cumulative amount when I drill through?

 

 

The DAX formula for the line is: 

Cumulative Incidents =

CALCULATE(

COUNTA('Table1'[Date Incident]),

FILTER(

        ALLSELECTED('Table 1'[Date Incident]),

        ISONORAFTER('Table1'[Date Incident], MAX('Table1'[Date Incident]), DESC)

    )

)

 
 
 
 
correctcorrectincorrect when I drill throughincorrect when I drill through
1 ACCEPTED SOLUTION

Okay, I got it to work by filtering other years and then using this:

 
CALCULATE(
    COUNTA('Table 1'[Date of Incident]),
    FILTER(
        ALL('Table 1'),
        ISONORAFTER('Table 1'[Date of Incident], MAX('Table 1'[Date of Incident]), DESC)
    )
)

View solution in original post

4 REPLIES 4
Cmcmahan
Resident Rockstar
Resident Rockstar

Try this:

Cumulative Incidents =

CALCULATE(

COUNTA('Table1'[Date Incident]),

FILTER(

        ALL('Table 1'[Date Incident]),
        'Table 1'[Date Incident].[Year] = SELECTEDVALUE('Table 1'[Date Incident].[Year]) &&
        ISONORAFTER('Table1'[Date Incident], MAX('Table1'[Date Incident]), DESC)

    )

)

ALLSELECTED returns all data currently being shown in your visual.  You want all data for the year, so I changed it to ALL and then added an extra condition to make sure you're only using data from the current year.

 

EDIT: Forgot right parens. Added in red.

It won't let me add the ".[YEAR]", it's grayed out when I try to type the formula suggested and it gives a message that a single value for variation [YEAR] for the column cannot be determined.  I do have more than one year worth of dates in that column. 

Okay, I got it to work by filtering other years and then using this:

 
CALCULATE(
    COUNTA('Table 1'[Date of Incident]),
    FILTER(
        ALL('Table 1'),
        ISONORAFTER('Table 1'[Date of Incident], MAX('Table 1'[Date of Incident]), DESC)
    )
)

I'm glad you got it!

 

My original solution missed a right parens, so I added it back.

 

I'm curious which .[Year] it was giving you a hassle for.  Was it the first or 2nd one?

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors