Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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

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 NameStore nameAccepted commandsTotal commands
AAA11
AAA11
AAA01
AAB11
AAB11
AAB11
AAC11
AAC11
AAC01
BBA11
BBA11
BBA11
BBA01
BBB11
BBB11
BBB01
BBC11
BBC11
BBC11

 

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 NameCommand Acceptancecount Stores with acceptance < 0.73
A0.772
B0.81

 
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 NameCommand Acceptancecount Stores with acceptance < 0.73
A0.7777777783
B0.83

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





 


2 ACCEPTED SOLUTIONS
Mohammad_Refaei
Solution Specialist
Solution Specialist

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

View solution in original post

daxer-almighty
Solution Sage
Solution Sage

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

View solution in original post

2 REPLIES 2
daxer-almighty
Solution Sage
Solution Sage

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
Solution Specialist
Solution Specialist

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors