Forum Discussion

Birdjo's avatar
Birdjo
Icon for Resolver II rankResolver II
9 years ago

%GT filtered by month

Hello,

I am trying to visualize the percentage of grand total sales filtered by month for the customers. Currently I have %GT and when I put it to a matrix where are rows are the customers I see accurate data. But when I try to filter the matrix by selected month from the bar chart with sales by months what happens is that Power BI Desktop takes the customers' selected month sales and gives %GT for the whole year. 

 

In example if a customer has $1M sales for the whole year while the whole sales are $10M, and I select a month from the bart chart which has sales of $2M, and the customer has sales of $100K it takes these $100K and gives me their %GT for the year ($10M), but not the month ($2M).

 

I will be very happy if someone who knows how to make it to give the %GT for the selected shares his knowledge.

 

Thank you in advance!

Need Help

5 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Birdjo,

    Based on your description, how do you filter the matrix by selected month from the bar chart. What’s the bar chart? In the bar chart, month is row level, right? “You select a month from the bar chart” means when you select one month, the matrix changes automatically, right? Could you please share the %GT measure or columns for further analysis?

    Best Regards,
    Angelia

    • Birdjo's avatar
      Birdjo
      Icon for Resolver II rankResolver II
      9 years ago

      Yes when I selected a month from the bar chart the matrix changes automatically. But what the matrix show is not the equity of a customer for a month but customers' equity given the sales for this month only and I get very small percentages like 1.23% when it should be much more. 

       

      To find the equity of each customer I use a calculated column with the following formula:

      %GT =
DIVIDE ( Invoiceses[InvoiceAmount], SUM ( Invoices[InvoiceAmount]) )

      P.S. : Actually what I do is to find the equity of each invoice and then then add it to the matrix with the customers.  

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi Birdjo,

        Based on my understanding, the record in your resource table is based on day level. I try to reproduce your scenario using the following sample data and get expected result.

         

        First, add a calculated column using the formula: Month = MONTH('Table'[Date])

         

        Then create a measure used to calculate the %GT using the formula.

         %GD = SUM('Table'[Value])/CALCULATE(SUM('Table'[Value]),ALL('Table'))

        Create a matrix, select the Customer field as row level, the Month field as column level, you will get the following result. In the row, it display the percentage of total of each customer in each month versus, the row total means the total of each customer versus total of all customers in all time. The column total means the percentage of total in each month versus the all total.



        Finally, I create a clustered column chart, select the month in axis level, the measure as value level. The var shows percentage of  each month sales sum versus the all total.




        Best Regards,
        Angelia