Forum Discussion

qmartiny's avatar
qmartiny
Helper III
4 years ago
Solved

Distinct Count not working when exporting to excel

Hello lovely people.

 

I have a matrix visual with the format below

 

Master AccountSub AccountExpected Distinct Count of Master
AAAA_11
AAAA_20
BBBB_11
BBBB_20

 

I have a Card visual that shows the distinct count of Master Accounts. ex Master Accounts = 2 as expected

 

I have added a column  "Distinct Count of Master Account." so that my user when exporting to excel will see the same count as the card visual.

 

However exporting the matrix in excel, the distinct count column just shows 1 for each row. I have also tried with a measure instead. No luck

 

Thank you for your help.

 

The user is on mac and cannot use the pivot table (add to data model) trick

 

Quentin

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi qmartiny ,

     

    If you want the first row of each Master Account to show the distinct count and other rows show 0, you could create a formula as below.

    Column = 
var _min = CALCULATE(MIN('Table'[BP_ID]),FILTER('Table','Table'[Top ID]=EARLIER('Table'[Top ID])))
var _count = calculate(DISTINCTCOUNT('Table'[Top ID]),FILTER('Table','Table'[Top ID]=EARLIER('Table'[Top ID])))
return
IF('Table'[BP_ID]=_min,_count,0)

     

    Best Regard,

    Jay

3 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi qmartiny 

     

    Can you share a sample of your data?

    BTW, try this measure:

    Measure = CALCULATE(DISTINCTCOUNT('Table'[Master Account]),ALL('Table'))
     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/


     

    • qmartiny's avatar
      qmartiny
      Helper III
      4 years ago

      Hello,

       

      Here's the expected result. I want the user to be able to use the column 'Expected Result' as a field in a pivot table to get the distinct count of TOP_ID. It doesn't need to look like this but that's the way I picture a distinct count working

       

      At the moment the Distinct Count built in on PowerBI does not work and writes 1 for each line.

       

      Using CALCULATE(ALLSELECTED) works but gives me the total, repeated for each line. I want to avoid confusion for the user.

       

      They are filtering the data in this table before exporting, hence why I used ALLSELECTED.

       

      Really confused why the built in distinct count is not working.,.,

       

      Thanks for your help

       

       

      BP_IDTop IDExpected Result 
      100090001
      100190000
      100290000
      100380001
      100480000
      100580000
      100680000
      100780000
      100850001
      100950000
      101050000

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi qmartiny ,

     

    If you want the first row of each Master Account to show the distinct count and other rows show 0, you could create a formula as below.

    Column = 
var _min = CALCULATE(MIN('Table'[BP_ID]),FILTER('Table','Table'[Top ID]=EARLIER('Table'[Top ID])))
var _count = calculate(DISTINCTCOUNT('Table'[Top ID]),FILTER('Table','Table'[Top ID]=EARLIER('Table'[Top ID])))
return
IF('Table'[BP_ID]=_min,_count,0)

     

    Best Regard,

    Jay