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
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?
Capture.PNG
 
1 ACCEPTED SOLUTION
v-lid-msft
Community Support
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.

 

25.jpg

 

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

 

26.jpg

 

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
)

 

27.jpg28.jpg

 


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.

View solution in original post

12 REPLIES 12
georgina261185
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]))

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?

 

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

 

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]

)))

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)

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 Snapdrag_0-1712656883712.png

 

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

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
Community Support
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.

 

25.jpg

 

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

 

26.jpg

 

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
)

 

27.jpg28.jpg

 


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.

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?

 

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.
Capture.PNG

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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