Divide filtered count by the total count

[horrellbt01]

I am having a hard time writing this measure.

I have a table called Stores
In that table I have a column named TM and a column named "StoreType"

I need a measure that divides the number of times the word "open" appears in the TM column by the count of the specific store type.

Example:

StoreType	TM		Desired Result
A	Open 9-5		Store Type	Open %
A	Closed		A	50%​
B	Open 10-2		B	75%​
B	Renovation		C	50%​
B	Open 7-4
C	Open
C	Open during construction
C	Closed permanently
C	Unknown

 

[denzo36]

how about the attached?

FYI for B your result should be 66.6% as it open 2 out 3

You'll need to change the formula for B and C in your criteria for the countif/countifs as shown only shows A

 

[horrellbt01]

how about the attached?

FYI for B your result should be 66.6% as it open 2 out 3

View attachment 32444

That absolutely works, but not looking for an excel based solution. Need this as a Dax measure.

 

[I_cant_read]

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StoreType", type text}, {"TM", type text}}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter([TM], " ", 0), type text),
#"Grouped Rows" = Table.Group(#"Inserted Text Before Delimiter", {"StoreType", "Text Before Delimiter"}, {{"Count", each Table.RowCount(_), type number}, {"MyData", each _, type table}}),
#"Expanded MyData" = Table.ExpandTableColumn(#"Grouped Rows", "MyData", {"TM"}, {"TM"}),
#"Grouped Rows1" = Table.Group(#"Expanded MyData", {"StoreType"}, {{"Count", each Table.RowCount(_), type number}, {"MYData", each _, type table}}),
#"Expanded MYData" = Table.ExpandTableColumn(#"Grouped Rows1", "MYData", {"Text Before Delimiter", "Count", "TM"}, {"Text Before Delimiter", "Count.1", "TM"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded MYData",{"StoreType", "TM", "Text Before Delimiter", "Count.1", "Count"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Text Before Delimiter] = "Open")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Percentage", each [Count.1]/[Count]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Percentage", Percentage.Type}}),
#"Grouped Rows2" = Table.Group(#"Changed Type1", {"StoreType", "Percentage"}, {{"Final Data", each _, type table}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows2",{"Final Data"})
in
#"Removed Columns"

Not a measure but M-Code... (Extracted from advanced editor)

Table1 is the name of the table which is containing the first two columns of your example. Columns [StoreType] and [TM]
(not exactly a DAX but rather an alternative. and might take a large amount of computation time if used in a large data set)

 

[I_cant_read]

If you want a more elegant approach.

You would need to do the ff:
1.)Create a Query containing your table which has a custom column containing the ff:
=(Text.Contains([TM],"Open"))
This should return a binary (True/False) >then manually change type into a number > then group and add up the binary

2.)Create a new Query which is grouped and count rows.
This should return a table which consists of the number of counts of A, B, and C.

3.)Merge Query1 and Query 2 using Left outer tada! You have your total of open stores and total of number of stores. Just add Metric or Add a new column.

I hope this helps.