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 a...
Need Help
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


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

v-huizhn-msft thank you very much for trying to help me but I'm not sure that you understand what I am looking for. Here I made some screen shot maybe you will understand me better

 

 

So what I am trying to get here is when I select a month I would like to see the equity in percentage of a given customer only for that exact month from the total sales for the month, not for the year.

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

    Hi Birdjo，

    I know what you want, but each month have multiple customers. In you select one month it will filter the table according to selected month. If we use same measure %GT in chart and table, we are not able to display percentage of a given customer only for that exact month.

    Thanks,
    Angelia