Percentage of category total modifiable by slicers

[Ariana]

Hello all,

I have a table with sales data divided by date, category and subcategory like the following: 

Date	Category	Subcategory	Sales
10-Feb	Electronics	TV	500
10-Feb	Electronics	Audio	300
10-Feb	Electronics	Phones	200
10-Feb	Home appliances	Washing machine	600
10-Feb	Home appliances	Oven	900
10-Feb	Home appliances	Refrigerator	700
11-Feb	Electronics	TV	400
11-Feb	Electronics	Audio	500
11-Feb	Electronics	Phones	200
11-Feb	Home appliances	Washing machine	100
11-Feb	Home appliances	Oven	300
11-Feb	Home appliances	Refrigerator	600
12-Feb	Electronics	TV	400
12-Feb	Electronics	Audio	800
12-Feb	Electronics	Phones	200
12-Feb	Home appliances	Washing machine	900
12-Feb	Home appliances	Oven	700
12-Feb	Home appliances	Refrigerator	100
13-Feb	Electronics	TV	300
13-Feb	Electronics	Audio	400
13-Feb	Electronics	Phones	200
13-Feb	Home appliances	Washing machine	600
13-Feb	Home appliances	Oven	500
13-Feb	Home appliances	Refrigerator	400

I am trying to group this data by date and category and get the percentage of the total by category; in other words, the share of sales per date by each category. This way the percentage of the total adds up to 100% per category like shown in the next table.

Date	Category	Total	% of Total
10-Feb	Electronics	1000	23%
11-Feb	Electronics	1100	25%
12-Feb	Electronics	1400	32%
13-Feb	Electronics	900	20%
10-Feb	Home appliances	2200	34%
11-Feb	Home appliances	1000	16%
12-Feb	Home appliances	1700	27%
13-Feb	Home appliances	1500	23%

I have been trying to achieve this result with the next measure, however this measure does not work when I modify the dates with a slicer.

%Total =
VAR inter =
    ADDCOLUMNS (
        SUMMARIZE ( 'Sales', 'Sales'[Dates].[Date], 'Sales'[Category] ),
        "Sls", CALCULATE ( SUM ( 'Sales'[Sales] ) ),
        "Sls_Total", CALCULATE ( SUM ( 'Sales'[Sales] ), ALLEXCEPT ( 'Sales', 'Sales'[Category] ) )
    )
RETURN
    SUMX ( inter, [Sls] / [Sls_Total] )

When I use the date slicer with this measure, I get the wrong results because the total sales per category are not filtered by the slicer. Here's an example of the results I would get with this measure filtering for only two dates, and the results I need:

Date	Category	Total	Wrong result with measure	Correct result
11-Feb	Electronics	1100	25%	44%
12-Feb	Electronics	1400	32%	56%
11-Feb	Home appliances	1000	16%	37%
12-Feb	Home appliances	1700	27%	63%

I believe this happens because of the allexcept function but I don't know how else to get the total per category. I've been searching all through the forums but I can´t find an answer.

Any help as to how to get these results would be greatly appreciated. 

Thank you.

[v-deddai1-msft]

Hi @Ariana ,

Would you please try the following measure:

Measure = SUM('Table'[Total])/CALCULATE(SUM('Table'[Total]),FILTER(ALLSELECTED('Table'),'Table'[Category] IN DISTINCT('Table'[Category])))

Best Regards,

Dedmon Dai

[Ariana]

@amitchandak  I am sorry, I can't open the file. I get an error saying that the file can't be opened because it's not compatible with my Power BI version. Unfortunately I am unable to update my Power BI version (2.82.5858.641).

[amitchandak · Accepted solution]

@Ariana , please find the formula ans screen shot

% of sub = divide(sum('Table'[Total]), CALCULATE(sum('Table'[Total]), FILTER(ALLSELECTED('Table'), 'Table'[Category] =max('Table'[Category]))))

[amitchandak]

@Ariana , Please find the file attached after signature

I have tested with date filter

[Ariana]

Thank you @amitchandak. I tried it as you suggested but now I get 100% on every day. I guess it's not dividing by the total of the category. 

[amitchandak]

@Ariana ,

Try a measure like

divide(sum(table[sales]), calculate(sum(Table[sales]), filter(allselected(Table), Table[Category] =max(Table[Category]))))

The below one might not consider any other filter than category

divide(sum(table[sales]), calculate(sum(Table[sales]), allexcept(Table, Table[Category])))

[AllisonKennedy]

I'm not sure about using both in the same measure, but try ALLSELECTED(Date) ;

%Total =
VAR inter =
    ADDCOLUMNS (
        SUMMARIZE ( 'Sales', 'Sales'[Dates].[Date], 'Sales'[Category] ),
        "Sls", CALCULATE ( SUM ( 'Sales'[Sales] ) ),
        "Sls_Total", CALCULATE ( SUM ( 'Sales'[Sales] ), ALLSELECTED(Date), ALLEXCEPT ( 'Sales', 'Sales'[Category] ) )
    )
RETURN
    SUMX ( inter, [Sls] / [Sls_Total] )

Note, this assumes you have a date table, which you haven't mentioned: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html  

[Ariana]

Thank you, Allison. I didn't have a date table. I created one based on the link you gave me and added the DateKey on my sales table. Then I tried using allselected in the measure as you suggested, but it still shows the same result. 

%Total =
VAR inter =
    ADDCOLUMNS (
        SUMMARIZE ( 'Sales', 'Sales'[Dates].[Date], 'Sales'[Category] ),
        "Sls", CALCULATE ( SUM ( 'Sales'[Sales] ) ),
        "Sls_Total",
            CALCULATE (
                SUM ( 'Sales'[Sales] ),
                ALLSELECTED ( DimDate[Date] ),
                ALLEXCEPT ( 'Sales', 'Sales'[Category] )
            )
    )
RETURN
    SUMX ( inter, [Sls] / [Sls_Total] )

[AllisonKennedy]

@Ariana Okay, let's simplify your measure a bit. If you simply want Percent of Total Sales within each Category, for allselected date range, then we can use CALCULATE to modify filter context and DIVIDE function for percent. The grouping can be done by how you use the measure in your visual.

Try: 

Total Sales = SUM(Table[Total])

Percent Total Sales by Category =
DIVIDE(
[TotalSales],
CALCULATE([Total Sales], ALLSELECTED(Date))

)

You may need/want to add other filters to the CALCULATE function, such as ALL(Table[Subcategory]) depending on how you use this measure.