cancel
Showing results 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

Anonymous
Not applicable

## Drill through for calculated measure

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?

1 ACCEPTED SOLUTION
Community Support

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,

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.
12 REPLIES 12
Frequent Visitor

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

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?

Frequent Visitor

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

Helper II

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]

)))

Frequent Visitor

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)
Helper II

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

Frequent Visitor

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

Helper II

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

Community Support

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,

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.
New Member

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?

New Member

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

Anonymous
Not applicable

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors