Barchart showing % sales of each month by total sales for the year based on slicer selected dynamica

[Anonymous]

Hi all,

I am trying to use the bar chart to show the percentage of gross sales in each month for a product compare to the selected year, segment, country.

I created one measure object:  

• test_measure = SUMX('Financial Sample','Financial Sample'[ Gross Sales ])

The above measure will give me the nominator value accurately when I select the relevant slicer value (Segment, Country, Product, and Date) because PBI will group the total gross sales figure (i.e. based on the selected value in the four slicers) and club the figure into the various month bucket correctly.

field setting in Power BI

Probem:

As my objective is to show the % gross sales of each month which is I need to divide the Measuare object (i.e. test_measure describe above)  BY the Gross Sales for the whole year (in this case 2014) and also based on other slicers selected value.

Hence, based on the above slicer selected value (i.e. Sector: Government, Country: USA, Product:  Amarilla, and Year: 2014) , the test_measure should have the following DAX coding...

•  Nominator =  SUMX('Financial Sample','Financial Sample'[ Gross Sales ])
  
•  Denominator = SUMX('Financial Sample','Financial Sample'[ Gross Sales ])

Obviously, the above measure will show value 1 in each month in barchart and it is wrong in spite of the DAX function in denominator  will give me the correct value if I do a quick test on the number (see following last figure).

Question - 
Unlike the nominator, how do I tell the POWER BI not to club the denominator value into individual month so that each of the gross sales figure in each month (nominator) will be divided by the total denominotaor value (i.e. the whole year value based on slicers value by Segment: Government, Country: USA, Product:  Amarilla, and Date:2014). It seem, POWER BI, treat the denominator calculation as monthly.

I tried to use ALL function but I cannot disregard the filter criteria selected in all slicers because the denominator value has to take into consideration of all slicer value NOT only in year value. 

I know this problem can be easily resolved if I, first, create a table layout to calculate the denominator value (show value as ==> percentage of grand total) and transfer the calculated table into a Barchart. However, I am just thinking from a DAX perspective to resolve it.

I know I miss out something. Hope you can shed some light. Thanks ,,tuffy

Compared to the dataset figure

[v-danhe-msft]

Hi @Anonymous,

From your description, I could not figure out which filter you want to apply in your denominator? Or you could refer to use the ALLEXCEPT function for your denominator to meet your requirements:

Reference:https://docs.microsoft.com/en-us/dax/allexcept-function-dax

I suggest you offer me a sample data to have a test and post your desired result if possible.

Regards,

Daniel He