Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ----->
Company Name | Command Acceptance | count Stores with acceptance < 0.73 |
A | 0.777777778 | 3 |
B | 0.8 | 3 |
Solved! Go to 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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |