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 
Hi the summarized matrix should look like this :

why on your example AA level is 50 if the total sum is 15?

DevMaster's avatar
DevMaster
Regular Visitor
3 years ago

thanks for your reply.

 

I am calculating average (5/10)*100 from backend and the data will come from SQL database. And i just want display the values only on Level2 & Level3

  • 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