Forum Discussion

DevMaster's avatar
DevMaster
Regular Visitor
3 years ago
Solved

Unable to display values in Level2 and Level3 Matrix visual in Power Bi

I am trying to create Level1, Level2 and Level3 matrix visual and unable to display custom calculations in Level2. below is the sample data to construct the visual.   Data: Level1 Level2 Leve...
Need Help
Tutorial Requests
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    If I understand you correctly and your desired result is : 

    You can modify it from the subtotals format with these steps:

    I also updated the linked file

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

  • DevMaster's avatar
    DevMaster
    3 years ago

    Thanks a lot. it resolves all my issues.

Ritaf1983's avatar
Ritaf1983
Icon for Super User rankSuper User
3 years ago

Hi DevMaster  again 🙂

I'm still not sure I understand.
But if you just want to show levels 2 and 3 you can achieve it with DAX
According to the hierarchy level, it shows the calculation that you "tell" him

SunValue = if (ISFILTERED('Table'[Level2]),sum('Table'[Value]),if(ISFILTERED('Table'[Level1]),BLANK()))

 


If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

DevMaster's avatar
DevMaster
Regular Visitor
3 years ago

i am trying to calculate the percentage and show the values in Level2. Could you please share .pbix file 

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    3 years ago

    Hi DevMaster 
    If I understand you correctly and your desired result is something like this :



    Then you can follow these steps:
    1. Add a calculated column for percentage:

    Percentage =
     VAR __Current = 'Table'[Value]
      VAR __Previous = MAXX(FILTER('Table','Table'[Level2] = EARLIER('Table'[Level2])),[Level2])

      VAR __PreviousValue = MAXX(FILTER('Table',[Level2]=__Previous),[Value])
    RETURN
    DIVIDE( __Current,__PreviousValue)

     

    2. Create a measure for the matrix:

    MatrixValue = if (ISFILTERED('Table'[Level3]),SUM('Table'[Value]),
    if(ISFILTERED('Table'[Level2]),format(min('Table'[Percentage]),"00%"),
    blank()))

    Link to a sample file 


    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

    • DevMaster's avatar
      DevMaster
      Regular Visitor
      3 years ago

      Thank you for solution. Here i am facing one issue like i don't want to show Total text in the report. if i set subtotals option to off then values not showing. 

       

      Could you please suggest that how we need to get rid off row/column subtotals.

      • Ritaf1983's avatar
        Ritaf1983
        Icon for Super User rankSuper User
        3 years ago

        If I understand you correctly and your desired result is : 

        You can modify it from the subtotals format with these steps:

        I also updated the linked file

         

        If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly