Summing distinct counts of lower granularity

[bendarr]

Hi all,

Been beating my head against this all morning and still haven't been able to come up with a proper solution.  What I have is a data set reflecting distinct user actions to various test scenarios within a given Split Test.  What I need is to sum those distinct user counts for the various test scenarios up to the Split Test.

The best I have been able to come up with is to calculate my distinct count measure (TOT_VISITS), and remove the lower granuliarty filtering that is in my matrix. 

TOT_VISITS_NO_URL_VARIATION_DATE =

CALCULATE(

[TOT_VISITS],

REMOVEFILTERS(

RTFN_SUBREGACTIONSTATUS_SPLITTESTS[SPLIT_TEST_URL],

RTFN_SUBREGACTIONSTATUS_SPLITTESTS[VARIATION_DESCRIPTION],

RTFN_SUBREGACTIONSTATUS_SPLITTESTS[SPLIT_TEST_CREATED_AT]

)

)

This works for most, except the scenarios where I have users that managed to go through multiple scenarios on a single Split Test.  In this case, they get deduped at the higher Split Test granularity, and my counts are off. 

See the below screenshot for Split Test 1729.  The expected total for 

TOT_VISITS_NO_URL_VARIATION_DATE is 473, however I am getting 462 my measure deffinition is forcing the distinct count to be done at the Split Test granularity instead of maintaining the Created Date granularity and summing those totals.  Which I get, because I am removing the filters in the calc.

I have tried summarizing the set then doing a sumx off of that, as well as a sumx from the values and haven't had any luck this far.  Anything to point me in the right direction would be appreciated.

[bendarr · Accepted solution]

@Greg_Deckler I was able to figure it out using a really old answer of yours

https://community.fabric.microsoft.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/28665

So basically I created a new column to get a unique value for the variation and the user, then utilized that distinct count within my original calculation removing the filters.

Thanks for your 7 year old answer in the archive!

[Greg_Deckler]

@bendarr Sweet!

[Greg_Deckler]

@bendarr Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[bendarr]

@Greg_Deckler 

Sorry, the below screenshot is an export of the data as it currently stands with my measure that removes filters

Using the 1729 Split Test as an example, the counts are coming up short because the users are going through different variations of the same split test.  So the distinct count at the Variation granularity would include them, but the distinct count at the Split Test Granulartiy dedupes them.  What I am looking for is the TOT_VISITS_NO_URL_VARIATION_DATE measure to return the sum of the distinct counts for the variation granularity.

Meaning, for 1729, I should be seeing 473 for each row (250+1+222), displaying like the below