Count of items that fall within the lowest 5% of Sales over 12 Months

[Anonymous]

I am looking for a way to count the number of items that fall within the lowest 5% of total sales over a 12 month period.  

[nandukrishnavs]

@Anonymous 

I have created a sample data. Below is the PQ

Table name: MyTable

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdFJDoQwDETRu2RNS7HJ5GUPt0Dc/xodObJNRYLVB56oXFfK9JoXZxrpSO95U87pPlZgCx8NFOW08tXCUYqVn5bTQ0WleGio1PhWR6VFGah0D4LKsEAZFfFvEYHy+H9iUIhc0VfEFGJcTEyhc1tMXCnbYuJKxcVCabhYKH1bLJSxLRaK4GKuxEmuxVxh2hZzhXlbzBU+YbH5nCkFFtOwlIqLaVlKw8W0LKXDYg9lnv79Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product", type text}, {"Sales", Int64.Type}})
in
    #"Changed Type"

 Dax measure 

Measure = 
var _GP= SUMMARIZE(FILTER(MyTable,MyTable[Date]>=TODAY()-365),MyTable[Product],"Sales",SUM(MyTable[Sales]))
var _TotalSales= SUMX(_GP,[Sales])
var _fivepercentage= 0.05*_TotalSales
var _count=COUNTROWS(FILTER(_GP,[Sales]<_fivepercentage))
return _count

You can use below DAX in a calculated table to validate the result.

Table = SUMMARIZE(FILTER(MyTable,MyTable[Date]>=TODAY()-365),MyTable[Product],"Sales",SUM(MyTable[Sales]))

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

Hi, please provide Powet BI file. 

Thank you