Forum Discussion

jwdal's avatar
jwdal
Frequent Visitor
8 months ago
Solved

Bar graph issue

My matrix has net sales by top 10 customers. The graph shows values for all customers. I want the graph to show values as displayed in the matrix. Sample 

Need Help
  • v-dineshya's avatar
    v-dineshya
    8 months ago

    Hi jwdal ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    As you mentioned in your query, you want the graph to show values as displayed in the matrix.

     

    Please refer below DAX measure.

     

    Net Sales (Top10 Customers Selected) =
    VAR N = 10
    VAR ExcludeCustomer = "OXYCHEM"

    VAR TopCustomersTable =
        TOPN(
            N,
            FILTER(
                SUMMARIZE(
                    ALLSELECTED(AC_ALL),            
                    AC_ALL[Top10],                
                    "CustomerSales", [Net_Sales]    
                ),
                AC_ALL[Top10] <> ExcludeCustomer
            ),
            [CustomerSales],
            DESC
        )

    VAR TopCustomersSingleColumn =
        SELECTCOLUMNS(TopCustomersTable, "Cust", AC_ALL[Top10])

    RETURN
    CALCULATE(
        [Net_Sales],
        KEEPFILTERS(
            TREATAS(TopCustomersSingleColumn, AC_ALL[Top10])
        )
    )
     
    Please refer below output snaps.
     
    For the year 2016 ,  the Matrix shows the value as "646" and the bar chart value as "646", both are same.
     

     

    For the year 2017 ,  the Matrix shows the value as "624" and the bar chart value as "624", both are same.

     

     

     If you want , i will share .PBIX file.

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

     

9 Replies

  • Mauro89's avatar
    Mauro89
    Super User
    8 months ago

    Hi jwdal,

     

    you mean like this?

     

    Best regards!

    PS: If you find this post helpful consider leaving kudos or mark it as solution

  • jwdal's avatar
    jwdal
    Frequent Visitor
    8 months ago

    No I want the graph to show business group but the total for all of the business group bars should be what's showing up in the matrix.

    • Mauro89's avatar
      Mauro89
      Super User
      8 months ago

      Hi jwdal,

       

      ok got it, then I think you just need to add the "top ten" column as a visual filter and select as filter type "Top N" and then 10. See the screenshot below.

       

      Hope this is what you are searching for?

      • Mauro89's avatar
        Mauro89
        Super User
        8 months ago

        jwdal if this solved you issue, feel free to mark the post as solution ðŸ˜Š

  • jwdal's avatar
    jwdal
    Frequent Visitor
    8 months ago

    Close.  The sum of the bar graph values should total 623.9874

     

     

     

    • Mauro89's avatar
      Mauro89
      Super User
      8 months ago

      jwdal the difference may come from the fact that the column "Top10" is different regarding the filter context compared to your measure.

       

      Check if this is the difference and let us know.

  • v-dineshya's avatar
    v-dineshya
    Community Support
    8 months ago

    Hi jwdal ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    As you mentioned in your query, you want the graph to show values as displayed in the matrix.

     

    Please refer below DAX measure.

     

    Net Sales (Top10 Customers Selected) =
    VAR N = 10
    VAR ExcludeCustomer = "OXYCHEM"

    VAR TopCustomersTable =
        TOPN(
            N,
            FILTER(
                SUMMARIZE(
                    ALLSELECTED(AC_ALL),            
                    AC_ALL[Top10],                
                    "CustomerSales", [Net_Sales]    
                ),
                AC_ALL[Top10] <> ExcludeCustomer
            ),
            [CustomerSales],
            DESC
        )

    VAR TopCustomersSingleColumn =
        SELECTCOLUMNS(TopCustomersTable, "Cust", AC_ALL[Top10])

    RETURN
    CALCULATE(
        [Net_Sales],
        KEEPFILTERS(
            TREATAS(TopCustomersSingleColumn, AC_ALL[Top10])
        )
    )
     
    Please refer below output snaps.
     
    For the year 2016 ,  the Matrix shows the value as "646" and the bar chart value as "646", both are same.
     

     

    For the year 2017 ,  the Matrix shows the value as "624" and the bar chart value as "624", both are same.

     

     

     If you want , i will share .PBIX file.

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

     

    • v-dineshya's avatar
      v-dineshya
      Community Support
      8 months ago

      Hi jwdal ,

      We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

       

      Regards,

      Dinesh

      • v-dineshya's avatar
        v-dineshya
        Community Support
        8 months ago

        Hi @jwdal ,

        We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

         

        Regards,

        Dinesh