how to distinct count based on lower granularity than shown in powerbi visual

[Anonymous]

I’m having trouble “thinking in DAX” WRT counting distinct stores based on a filter condition (catch - stores are not part of the visual canvas) 

I have a table, sadly cant show the real data here due to privacy concerns , it is something like this [Table name - Company]

Company Name	Store name	Accepted commands	Total commands
A	AA	1	1
A	AA	1	1
A	AA	0	1
A	AB	1	1
A	AB	1	1
A	AB	1	1
A	AC	1	1
A	AC	1	1
A	AC	0	1
B	BA	1	1
B	BA	1	1
B	BA	1	1
B	BA	0	1
B	BB	1	1
B	BB	1	1
B	BB	0	1
B	BC	1	1
B	BC	1	1
B	BC	1	1

I want to produce a visualization of a table that goes as follows [It can have only three columns as shown below]
{I calculated a measure 'Command acceptance' = sum(accepted commands)/sum(total commands) - this is on company level

Company Name	Command Acceptance	count Stores with acceptance < 0.73
A	0.77	2
B	0.8	1

 
Problem - I want to calculate the third column "count stores with acceptance < 0.73" 
This column should distinct count the stores whose command acceptance is less than 0.73 (accepted/total) 
In the example show abouve,
Company A has two stores namely, "AA" and "AC" whose command acceptance is less than 0.73 
Company  B has 1 store namely , "BB" whose command acceptance is less than 0.73

Third column should distinct count on store level of command acceptance. 

What i tried -----> 

CALCULATE(DISTINCTCOUNT(Company[store_name]),FILTER(company,[Command_Acceptance] < 0.73))

It gives me 

Company Name	Command Acceptance	count Stores with acceptance < 0.73
A	0.777777778	3
B	0.8	3

What am i doing wrong ? Any help is greatly appreciated. 

[Mohammad_Refaei · Accepted solution]

You should summarize the values of both company/store againist Command Acceptance then count the row or records.

Count Stores with acceptance < 0.73 =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Company, Company[Company Name], Company[Store name] ),
            "StoreCount", [CommandAcceptance]
        ),
        [CommandAcceptance] < 0.73
    )
)

Check this sample file

[daxer-almighty · Accepted solution]

Here you've got it. The parameter Threshold can be made dynamic.

[Stores with CA < Threshold] = // CA - command acceptance
var Threshold = .73
var Result =
    SUMX(
        SUMMARIZE(
            Company,
            Company[Company Name],
            // This grouping wouldn't be needed
            // if Stores had a unique StoreID.
            // but I have to assume that it's not
            // so and only what you've shown is
            // actually what you work with.
            Company[Store Name]
        ),
        CALCULATE(
            var AcceptedComs =
                SUM( Company[Accepted Commands] )
            var TotalComs =
                SUM( Company[Total Commands]
            var Result_ =
                AcceptedComs < Threshold * TotalComs
            return
                DIVIDE( Result_, Result_ )
        )
    )
return
    Result

[Mohammad_Refaei · Accepted solution]

You should summarize the values of both company/store againist Command Acceptance then count the row or records.

Count Stores with acceptance < 0.73 =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Company, Company[Company Name], Company[Store name] ),
            "StoreCount", [CommandAcceptance]
        ),
        [CommandAcceptance] < 0.73
    )
)

Check this sample file