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

     

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