Sum unique ID's within groups and in totalCreating DAX peer measureMeasure to sum another aggregated measure's dataDAX/PowerBI - Average Group ValueAverage of Grouped SumDAX Cumulative Sum, reset by x periodDAX grouping by a measure resultRanking measure by Grouped by Count Showing 1 in Visualization - PowerBISUM values on certain conditionsUnexpected result in DAX SUMX over GROUPBYNeed an average grand total but have a sum
"DDoouubbllee ssppeeaakk!!"
Why are sugars in whole fruits not digested the same way sugars in juice are?
Why do we need a voltage divider when we get the same voltage at the output as the input?
Why is “deal 6 damage” a legit phrase?
What is the significance of $(logname)?
Feedback diagram
UX writing: When to use "we"?
How to derive trigonometric Cartesian equation from parametric
Adjective for when skills are not improving and I'm depressed about it
What are the effects of the elements on 3D printed objects made with "infused" PLA
Bash Script read -p
Is this popular optical illusion made of a grey-scale image with coloured lines?
Can machine learning learn a function like finding maximum from a list?
mv Command Deleted Files In Source Directory and Target Directory
When did J.K. Rowling decide to make Harry and Ginny a couple?
Were there any unmanned expeditions to the moon that returned to Earth prior to Apollo?
How does Asimov's second law deal with contradictory orders from different people?
Backpacking with incontinence
Word for giving preference to the oldest child
Conflict between senior and junior members
Why are prop blades not shaped like household fan blades?
Can black block with a hanging piece in a back rank mate situation?
How do I safety check that there is no light in Darkroom / Darkbag?
A game of red and black
Sum unique ID's within groups and in total
Creating DAX peer measureMeasure to sum another aggregated measure's dataDAX/PowerBI - Average Group ValueAverage of Grouped SumDAX Cumulative Sum, reset by x periodDAX grouping by a measure resultRanking measure by Grouped by Count Showing 1 in Visualization - PowerBISUM values on certain conditionsUnexpected result in DAX SUMX over GROUPBYNeed an average grand total but have a sum
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I'm trying to count the number of unique ClientID in given time period in PowerBI. The trick is that the client can be a member of one or more groups and if he/she is in more then one group I need the sum to count his/her ID as one for each group.
My basic idea was to use measure to sum "ones" for each unique ClientID in fact table in given time period.
CALCULATE(
SUMX(
VALUES('fact'[ClientID]); 1
);
DATESBETWEEN('fact'[actionDate]; [start]; [end])
)
It basically work, if client take many actions in given time period his ID is counted only once, but when the client is taking actions as a member of two or more groups in this time period - his ID should count as one for each group.
The GroupName is one of column in fact table as well.
powerbi dax
asked Mar 26 at 23:22
UzzyUzzy
1331 silver badge12 bronze badges
add a comment |
I'm trying to count the number of unique ClientID in given time period in PowerBI. The trick is that the client can be a member of one or more groups and if he/she is in more then one group I need the sum to count his/her ID as one for each group.
My basic idea was to use measure to sum "ones" for each unique ClientID in fact table in given time period.
CALCULATE(
SUMX(
VALUES('fact'[ClientID]); 1
);
DATESBETWEEN('fact'[actionDate]; [start]; [end])
)
It basically work, if client take many actions in given time period his ID is counted only once, but when the client is taking actions as a member of two or more groups in this time period - his ID should count as one for each group.
The GroupName is one of column in fact table as well.
powerbi dax
asked Mar 26 at 23:22
UzzyUzzy
1331 silver badge12 bronze badges
Have you triedDISTINCTCOUNT('fact'[ClientID])?
– Rory
Mar 27 at 8:34
DISTINCTCOUNT will not change anything. Still i will get ClientID counted only once even if it will be member of two or more groups.
– Uzzy
Mar 27 at 9:27
add a comment |
I'm trying to count the number of unique ClientID in given time period in PowerBI. The trick is that the client can be a member of one or more groups and if he/she is in more then one group I need the sum to count his/her ID as one for each group.
My basic idea was to use measure to sum "ones" for each unique ClientID in fact table in given time period.
CALCULATE(
SUMX(
VALUES('fact'[ClientID]); 1
);
DATESBETWEEN('fact'[actionDate]; [start]; [end])
)
It basically work, if client take many actions in given time period his ID is counted only once, but when the client is taking actions as a member of two or more groups in this time period - his ID should count as one for each group.
The GroupName is one of column in fact table as well.
powerbi dax
asked Mar 26 at 23:22
UzzyUzzy
1331 silver badge12 bronze badges
I'm trying to count the number of unique ClientID in given time period in PowerBI. The trick is that the client can be a member of one or more groups and if he/she is in more then one group I need the sum to count his/her ID as one for each group.
My basic idea was to use measure to sum "ones" for each unique ClientID in fact table in given time period.
CALCULATE(
SUMX(
VALUES('fact'[ClientID]); 1
);
DATESBETWEEN('fact'[actionDate]; [start]; [end])
)
It basically work, if client take many actions in given time period his ID is counted only once, but when the client is taking actions as a member of two or more groups in this time period - his ID should count as one for each group.
The GroupName is one of column in fact table as well.
powerbi dax
powerbi dax
asked Mar 26 at 23:22
UzzyUzzy
1331 silver badge12 bronze badges
asked Mar 26 at 23:22
UzzyUzzy
1331 silver badge12 bronze badges
asked Mar 26 at 23:22
UzzyUzzy
1331 silver badge12 bronze badges
asked Mar 26 at 23:22
UzzyUzzy
1331 silver badge12 bronze badges
asked Mar 26 at 23:22
UzzyUzzy
1331 silver badge12 bronze badges
1331 silver badge12 bronze badges
Have you triedDISTINCTCOUNT('fact'[ClientID])?
– Rory
Mar 27 at 8:34
DISTINCTCOUNT will not change anything. Still i will get ClientID counted only once even if it will be member of two or more groups.
– Uzzy
Mar 27 at 9:27
add a comment |
Have you triedDISTINCTCOUNT('fact'[ClientID])?
– Rory
Mar 27 at 8:34
DISTINCTCOUNT will not change anything. Still i will get ClientID counted only once even if it will be member of two or more groups.
– Uzzy
Mar 27 at 9:27
Have you tried
DISTINCTCOUNT('fact'[ClientID])?– Rory
Mar 27 at 8:34
Have you tried
DISTINCTCOUNT('fact'[ClientID])?– Rory
Mar 27 at 8:34
DISTINCTCOUNT will not change anything. Still i will get ClientID counted only once even if it will be member of two or more groups.
– Uzzy
Mar 27 at 9:27
DISTINCTCOUNT will not change anything. Still i will get ClientID counted only once even if it will be member of two or more groups.
– Uzzy
Mar 27 at 9:27
add a comment |
1 Answer
1
active
oldest
votes
How about something like:
CALCULATE(
COUNTROWS(SUMMARIZE('fact';'fact'[ClientID];'fact'[GroupID]));
DATESBETWEEN('fact'[actionDate]; [start]; [end])
)
answered Mar 27 at 10:54
mxixmxix
2,5689 silver badges16 bronze badges
Flawless victory! Thank You, works like charm. I was experimenting with SUMMARIZECOLUMNS, but no luck.
– Uzzy
Mar 27 at 12:53
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55367585%2fsum-unique-ids-within-groups-and-in-total%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
How about something like:
CALCULATE(
COUNTROWS(SUMMARIZE('fact';'fact'[ClientID];'fact'[GroupID]));
DATESBETWEEN('fact'[actionDate]; [start]; [end])
)
answered Mar 27 at 10:54
mxixmxix
2,5689 silver badges16 bronze badges
Flawless victory! Thank You, works like charm. I was experimenting with SUMMARIZECOLUMNS, but no luck.
– Uzzy
Mar 27 at 12:53
add a comment |
How about something like:
CALCULATE(
COUNTROWS(SUMMARIZE('fact';'fact'[ClientID];'fact'[GroupID]));
DATESBETWEEN('fact'[actionDate]; [start]; [end])
)
answered Mar 27 at 10:54
mxixmxix
2,5689 silver badges16 bronze badges
Flawless victory! Thank You, works like charm. I was experimenting with SUMMARIZECOLUMNS, but no luck.
– Uzzy
Mar 27 at 12:53
add a comment |
How about something like:
CALCULATE(
COUNTROWS(SUMMARIZE('fact';'fact'[ClientID];'fact'[GroupID]));
DATESBETWEEN('fact'[actionDate]; [start]; [end])
)
answered Mar 27 at 10:54
mxixmxix
2,5689 silver badges16 bronze badges
How about something like:
CALCULATE(
COUNTROWS(SUMMARIZE('fact';'fact'[ClientID];'fact'[GroupID]));
DATESBETWEEN('fact'[actionDate]; [start]; [end])
)
answered Mar 27 at 10:54
mxixmxix
2,5689 silver badges16 bronze badges
answered Mar 27 at 10:54
mxixmxix
2,5689 silver badges16 bronze badges
answered Mar 27 at 10:54
mxixmxix
2,5689 silver badges16 bronze badges
answered Mar 27 at 10:54
mxixmxix
2,5689 silver badges16 bronze badges
2,5689 silver badges16 bronze badges
Flawless victory! Thank You, works like charm. I was experimenting with SUMMARIZECOLUMNS, but no luck.
– Uzzy
Mar 27 at 12:53
add a comment |
Flawless victory! Thank You, works like charm. I was experimenting with SUMMARIZECOLUMNS, but no luck.
– Uzzy
Mar 27 at 12:53
Flawless victory! Thank You, works like charm. I was experimenting with SUMMARIZECOLUMNS, but no luck.
– Uzzy
Mar 27 at 12:53
Flawless victory! Thank You, works like charm. I was experimenting with SUMMARIZECOLUMNS, but no luck.
– Uzzy
Mar 27 at 12:53
add a comment |
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55367585%2fsum-unique-ids-within-groups-and-in-total%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Have you tried
DISTINCTCOUNT('fact'[ClientID])?– Rory
Mar 27 at 8:34
DISTINCTCOUNT will not change anything. Still i will get ClientID counted only once even if it will be member of two or more groups.
– Uzzy
Mar 27 at 9:27