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 in Power BI by joseandreslv
• 120 points
363 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 27 by Gitika
• 37,700 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
147 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
• 32,490 points
4,416 views
0 votes
1 answer

Can I create a Power BI report in Office 365 without my locally installed Office Excel?

Yess!! You can achieve this by using ...READ MORE

answered Sep 26, 2018 in Power BI by Kalgi
• 51,950 points
209 views
0 votes
1 answer

How do I format the KPI in Power BI

format the KPI by selecting the paint ...READ MORE

answered Oct 9, 2018 in Power BI by Kalgi
• 51,950 points
151 views
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 20, 2019 in Power BI by avantika
• 1,500 points
153 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 6 days ago in Power BI by Alisha
14 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 6 days ago in Power BI by Gitika
• 37,700 points
27 views
0 votes
1 answer

How to replace Blank with "0" in Power BI Visual?

Hi, Sujit You can do this in two ...READ MORE

answered Feb 7 in Power BI by Gitika
• 37,700 points
10,391 views