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

[Snapdrag]

Hey Georgina, Did you found any solution to the problem you mentioned: I have percentage variance measures but they show as decimals. Even I am also facing the same issue. Could you please help here?

[georgina261185]

Yes I did

Measure = if(HASONEVALUE('Measure Table'[Measure Name]),switch(SELECTEDVALUE('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],

"FCT - FY",[Full Year Number for 2023 Forecast],

"MTD vs LY %",FORMAT([MTD vs LY %],"0.0%"),

"MTD vs BUD %",FORMAT([MTD vs BUD %],"0.0%"),

"YTD vs LY %",FORMAT([YTD vs LY %],"0.0%"),

"YTD vs BUD %",FORMAT([YTD vs BUD %],"0.0%"),

"FCT - Periodic",[FCT - Periodic],

"FCT - YTD",[FCT - YTD],

"MTD vs FCT %",FORMAT([MTD vs FCT %],"0.0%"),

"YTD vs FCT %",FORMAT([YTD vs FCT %],"0.0%"),

"Forecast Remaining",[Forecast Remaining 2023],

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

"Budget Remaining",[Budget Remaining 2024]))

[Snapdrag]

I tried using format function in same measure then it returns an error stating that SUMX do not work with values of type strings.

SUMX(DISTINCT('Measure Table'[MeasureName]),SWITCH([MeasureName],

"Measure A",[A],

"Measure B",[B],

"Measure C",[C]

)))

[georgina261185]

That is probably why I changed the dax to HASONEVALUE.

In the drill down table I had to create a drill down table using parameters

Drill down measure = {

    ("TY - Periodic", NAMEOF('LOCAL MEASURES'[TY - Periodic]), 0),

    ("LY - Periodic", NAMEOF('LOCAL MEASURES'[LY - Periodic]), 1),

    ("TY - YTD", NAMEOF('LOCAL MEASURES'[TY - YTD]), 2),

    ("LY - YTD", NAMEOF('LOCAL MEASURES'[LY - YTD]), 3)

[Snapdrag]

Sorry Georgina, as I am new to Power BI, still not able to recollect all the points. Can you please guide me with one by one step if possible. I know you were able to achieve and I am just one step away 

[georgina261185]

Can you share some more information? It is hard without seeing what you have already.

Can any other users assist please? I am fairly new too

[Snapdrag]

Hi All!

I have a Matrix visual with Category in the column and multiple measures as Values.

I checked option "Switch values to rows" so I have my measures names on the rows.

I would like to have a Drill through so a user can see the Details behind each matrix cell. 

Is there a way to pass a chosen measure to a drill through Page? Category filter works fine, but I would like also for the Filter to "remember" what is the measure on the row and see the details behind.

One work around would be to create a seperate drill through page for each measure and apply a measure as drill thorugh field, but I have too many of them. 

I tried using Field Parameters gathering my measures and put it on the rows but it does not work

[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.