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
2,360 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, 2020 by Gitika
• 65,870 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
223 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,910 points
5,249 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
• 52,310 points
257 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
• 52,310 points
217 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
343 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,870 points
146 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
74 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,870 points
589 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,870 points
2,951 views