Drill through for calculated measure

[Anonymous]

hey experts i have multiple measures in my chart like one of them is:

Measure 1:

Awarded/Producing = CALCULATE(COUNT(Opportunities[Awarded Pipeline]),FILTER(Opportunities,Opportunities[Opportunity State]="Open"))+CALCULATE(COUNT(Opportunities[SCO stages (Lookup)]),FILTER(Opportunities,Opportunities[SCO stages (Lookup)]="Producing"),FILTER(Opportunities,Opportunities[Opportunity State]="Open"))+CALCULATE(COUNT(Opportunities[SCO stages (Lookup)]),FILTER(Opportunities,Opportunities[SCO stages (Lookup)]="Close"),FILTER(Opportunities,Opportunities[Opportunity State]="Open"))

lets say its count on chart is in sky blue bar that is 20.When i click on drillthrough it dsnt show those 20 records instead of it shows records based on visual or report filter passed to drillthrough pase.So how could i achieve thi to see records based on measures?

[georgina261185]

Hi! I have used this solution for my problem but I have a lot of measures.

I need to display them in a table and then allow the user to drill through from there.

2 questions

How can I custom sort the measures in the matrix table? At the moment it is alphatetical order.

I have percentage variance measures but they show as decimals.

This is my current DAX for the measure. I did try using FORMAT but it didn't work

Measure = sumx(DISTINCT('Measure Table'[Measure Name]),switch('Measure Table'[Measure Name],

"TY - Periodic",[TY - Periodic],

"LY - Periodic",[LY - Periodic],

"TY - YTD",[TY - YTD],

"LY - YTD",[LY - YTD],

"BUD - Periodic",[BUD - Periodic],

"BUD - YTD",[BUD - YTD],

"BUD - FY",[Full Year Number for Annual budgeting],

"MTD vs LY %",[MTD vs LY %],

"MTD vs BUD %",[MTD vs BUD %],

"YTD vs LY %",[YTD vs LY %],

"YTD vs BUD %",[YTD vs BUD %],

"Budget Remaining",[Budget Remaining]))

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Sorry for that, but we cannot drill through by the value of measure, if you are applying multi measures in a Funnel chart, we can createa table with measure name first, such as measure A, Measure B, the measure column is sorted by the sort column.

Create a measure as the value field of chart, the measure name column is group field of chart:

Measure = SUMX(DISTINCT('Measure Table'[MeasureName]),SWITCH([MeasureName],"MeasureA",[MeasureA],"MeasureB",[MeasureB]))

Then we create a measure applied in the visual filter of drill through page (set equal to 1) :

Visual Control = 
IF (
    DISTINCTCOUNT ( 'Measure Table'[MeasureName] ) = 1,
    SWITCH (
        SELECTEDVALUE ( 'Measure Table'[MeasureName] ),
        "MeasureA", IF ( CALCULATE ( [MeasureA], 'Table') <>BLANK(), 1, -1 ),
        "MeasureB", IF ( CALCULATE ( [MeasureB], 'Table') <>BLANK() ,1, -1 )
    ),
    1
)

By the way, PBIX file as attached.

Best regards,

[KumarRanjit]

Hi! An ingenious solution but I'm facing an issue -

The minute I add a row in "Table" with following values: - 

Name = A

Value = 2

On clicking "Drill Through" for A the output shows both A and B, like so: -

Name | Value

A         | 3

B         | 2

Why is this so? 

I'll appreciate if you could explain this output.

Why is value for B showing up when I'm drilling for values of A?

[jon6035]

Thank you for this, I think it's going to help me out alot.

[Anonymous]

@v-lid-msftits working perfect for a funnel chart and i am really thankfull..
but what if i have other charts in report and in thier axis i am having coumn values as in attached screen.
So how it would work for each bucket against each column value(Non-Shipping Line,Shipping Line).
Will be thankfull if you may guide.