Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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?
 
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

12 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

    • jon6035's avatar
      jon6035
      New Member
      3 years ago

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

    • KumarRanjit's avatar
      KumarRanjit
      New Member
      3 years ago

      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?

       

  • georgina261185's avatar
    georgina261185
    Frequent Visitor
    3 years ago

    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]))
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        georgina261185
        Frequent Visitor
        2 years ago

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