Drill Through to detailed report from Multiple Measures

[harib]

Hello All,

I have Summary and Detailed reports. I would like to drillthrough from summary to detailed report.

In the summary i have a Coloumn name is Priority and the values are Critical, Major,Minor. I want to know the count of each value and Cumulative total of the each value. So i have created a Measure for all the values like below.

Critical = CALCULATE(COUNT(Summary[PRIORITY]),Summary[PRIORITY]="Critical")

Major = CALCULATE(COUNT(Summary[PRIORITY]),Summary[PRIORITY]="Major")

Minor = CALCULATE(COUNT(Summary[PRIORITY]),Summary[PRIORITY]="Minor")

Cumulative Major Tickets = CALCULATE([Major],FILTER(ALL(Summary[REPORTING_PERIOD]),Summary[REPORTING_PERIOD]<=MAX(Summary[REPORTING_PERIOD])))

Cumulative Minor Tickets = CALCULATE([Minor],FILTER(ALL(Summary[REPORTING_PERIOD]),Summary[REPORTING_PERIOD]<=MAX(Summary[REPORTING_PERIOD])))

My requirement is i want to drillthrough to detailed report from the values of measures. If i right click on the any Column Lets say Major value (Januray 2019, 50) i should drillthrough to detailed report.  so i have given Reporting_Period in drilthrough field in the detailed report. but it's showing complete row count of that period like ( January 2019 ,Major 50 + Minor 19=  69). Actually it has to show only 50 records of that period of Major. Like that cumulative when i click on the Cumulatie Major tickets, let say January 2019, 99, i,m not getting the detailed report of Cumulative records.. It's showing only January 2019 values (50). It should give November 2018 to January 2019 (99 records.).

If anyone know the solution please provide. Attached sample file. 

https://drive.google.com/file/d/1brZTsnfqx0mTryREYkTrb5PreaC5mO8a/view?usp=sharing

Thanks

[v-lid-msft · Accepted solution]

Hi @harib ,

I am sorry that if you use table, drillthrough will only put one slicer such as date into detail report, we can use a matrix visual and put the priority into drillthrough field to meet your first requirement.

If you want to show cumulative value, we can use the following measure:

Cumulative Tickets = 
COUNTAX (
    FILTER (
        ALLSELECTED ( 'Summary' ),
        AND(Summary[REPORTING_PERIOD] <= MAX ( Summary[REPORTING_PERIOD] )
            , [PRIORITY] = MAX ( [PRIORITY] ))
    ),
    [PRIORITY]
)

But drillthrough with a range data according to the filer in measure is nearly impossible, We suggest you to use a slicer instead of drill through in such situation.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.