How can I make my visual distinct count total match the row by row sum when applying filters in PowerBI

0 votes

I have a report that includes sales codes and I need my visuals to show the distinct count of the sales codes. I used to filter by month and then realized than in the distinct count formula I need to force a row by row context, as whenever I applied the month filter the visual distinct count total didn´t match the row by row sum.

I applied this formula and when filtering by month, the row by row sum was equal to the distinct count total: 

SumX Measure = 
SUMX(
    VALUES( 'Dates Table'[YearMonthnumber] ),
    CALCULATE(
        DISTINCTCOUNT( '2020'[Sales Code] )
    )
)

However, now I need to use other filters rather than only the month, but when I filter according to  other variables, again I have the problem that the distinct count total is not matching the row by row sum. I need the visual to have the correct distinct count total (matching with the row by row sum) by selecting different filters.

 I just want the distinct count to be correct according to the filters I select, there are four filters from the same table (product, country, company, customer ID) and one filter from a dates table. I attach an example so you can better understand what I need to do, my file includes the filters I usually apply and other dax formulas I have tried: Distinct Count Error 

Best

JLV

Jul 25, 2020 in Power BI by joseandreslv
• 120 points
7,393 views

1 answer to this question.

0 votes

The COUNTROWS function can be used to count the unique values available in a column for the current filter context:

SumX Measure =
SUMX(
    VALUES( 'Dates Table'[YearMonthnumber] ),
    CALCULATE(

                COUNTROWS ( DISTINCT ( table[column] ) 

                   DISTINCTCOUNT ( table[column] ) ) 

  )

According to your query, this will be helpful.

answered Jul 28, 2020 by Gitika
• 65,890 points

Related Questions In Power BI

0 votes
1 answer

How can i see my plugin name in the Power BI Visual plugin List ?

there is a couple of errors in your ...READ MORE

answered Nov 8, 2018 in Power BI by Upasana
• 8,620 points
1,389 views
0 votes
0 answers

How can I count the distinct values in a column using Power BI?

How can I count the distinct values ...READ MORE

Oct 23 in Power BI by Evanjalin
• 2,560 points
59 views
0 votes
1 answer

How can I get a column value from previous row in Power Query?

Hi Sindhu, add this line as your ...READ MORE

answered Mar 18, 2019 in Power BI by Cherukuri
• 33,030 points
8,143 views
0 votes
0 answers
0 votes
1 answer

How do I label bar graph with different colors based on values from different slicers?

If the user selects only one value ...READ MORE

answered May 21, 2019 in Power BI by Avantika
• 1,520 points
1,455 views
0 votes
1 answer

Using DAX calculation how to calculate monthly budget till today in power bi Desktop?

You can make use of this: MTD Budget ...READ MORE

answered Sep 24, 2020 in Power BI by Gitika
• 65,890 points
1,812 views
0 votes
1 answer
0 votes
1 answer

How to get month name from month number in Power BI?

You can use: MonthName = FORMAT(DATE(1, [Num], 1), ...READ MORE

answered Sep 24, 2020 in Power BI by Alisha
6,226 views
0 votes
1 answer

In DAX, how do I RETURN the sum of a calculated column from a DAX Table Variable (created via ADDCOLUMN)?

You can access column variables of previously ...READ MORE

answered Oct 8, 2020 in Power BI by Gitika
• 65,890 points
4,502 views
0 votes
1 answer

How do I count rows in one table based on values in another table using DAX?

If the tables are related, this is ...READ MORE

answered Sep 24, 2020 in Power BI by Gitika
• 65,890 points
22,556 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP