Count Occurrence of values in a measure to calculate score %

[Argha]

HI All,

I have the following table visual-

Country	Product	Product Category	Status	Count_complete	Count_Incomplete	Correct/Incorrect
	S	S-A	In Store		1	Correct
Albania	Z	Z-A	In Store		2	Correct
Algeria	D	D-A	In Store		5	Correct
Algeria	E	E-A	In Store	6	1	Correct
Algeria	H	H-A	In Store	2	1	Correct
Andorra	Z	Z-A	In Store		1	Correct
Argentina	I	I-A	Few Stocks		2	Correct
Argentina	IN	IN-A	Few Stocks	1		Incorrect
Argentina	INT	INT-A	Few Stocks		1	Correct
Argentina	INV	INV-A	Few Stocks		2	Correct
Argentina	INV	INV-A	Few Stocks		4	Correct
Argentina	INV	INV-A	Few Stocks		2	Correct
Argentina	O	O-A	Few Stocks	1		Incorrect
Argentina	OP	OP-A	Few Stocks		2	Correct
Argentina	P	P-A	Few Stocks		3	Correct
Argentina	R	R-A	Few Stocks		1	Correct
Argentina	RE	RE-A	Few Stocks	2	2	Correct
Argentina	RI	RI-A	Few Stocks		2	Correct
Argentina	RIS	RIS-A	Few Stocks	1	8	Correct

The requirement is to count the number of 'Correct' from 'Correct/Incorrect' measure and show Correct % for each country out of total correct and Incorrect values.

Column 'Correct/Incorrect' is derived from below logic (both count_correct and count_incorrect are measures too calculated by using distinct count of other dimension values)-
If (count_complete)>=1 && (count_incomplete)>=1 then "Correct"
If isblank(count_complete) && (count_incomplete)>=1 then "Correct"

If (count_complete)>=1 && isblank(count_incomplete) then "Incorrect"
If isblank(count_complete) && isblank(count_incomplete) then blank()

I have tried multiple ways to write the dax but unable to get the accurate Correct% of each country out of the total. Any help on this would be appreciated.

Thanks in advance.

[rsbin]

@Argha ,

Please try this as a Measure:

Correct% = DIVIDE( CALCULATE( COUNTA( 'Table'[Correct/Incorrect] ),
                      ALLEXCEPT( 'Table', 'Table'[Country]),
                      FILTER( 'Table', 'Table'[Correct/Incorrect] = "Correct" )),  // Counts the number or Correct entries
                   CALCULATE( COUNTROWS( 'Table'),
                            ALLEXCEPT( 'Table', 'Table'[Country] )), //  Counts the number of rows for each Country.
                            0 )

Hope this is what you are looking for

Regards,

[Argha]

@rsbin Thank you for your quick response. However i won't be able to use 'Correct/Incorrect' as 'Table'[Correct/Incorrect] because this 'Correct/Incorrect' is itself a measure. Is there any other way to write the dax?

[rsbin]

@Argha ,

My suggestion would be turn your Correct / Incorrect from a Measure to a Calculated Column.

This value seems to be derived at the row or record level and there is no aggregation here.  Hence, a Calculated Column makes more sense to me.  Otherwise you are going to have to use temp variables and tables to get what you are after.

[Argha]

@rsbin , if i turn 'Correct/Incorrect' into calculated column, the rows that have both 'count_complete' and 'count_incomplete' will split in 2 rows. So, aggregation is required here. The table that i included in the post is the detail section of the dashboard and needs to show in the same way in dashboard. Since i am not very well versed with powerbi and Dax, if possible, could you please help me with the other way you mentioned to achieve this?

[v-jianpeng-msft · Accepted solution]

Hi, @Argha 

Thanks @rsbin , I have the following additions. I used the following sample data:

Based on the logic you provided to judge whether it is correct or not, I have created a measure as follows:

I use the following DAX expression to find the correct percentage for each country:

Correct percentage =
VAR _table =
    SUMMARIZE (
        'Sheet7',
        Sheet7[Country],
        'Sheet7'[ Product],
        'Sheet7'[Product Category],
        'Sheet7'[Count_complete],
        'Sheet7'[Count_Incomplete],
        Sheet7[Status],
        "iscorrect", [Measure]
    )
RETURN
    DIVIDE (
        COUNTAX ( FILTER ( _table, [iscorrect] = "Correct" ), COUNTROWS ( _table ) ),
        COUNTAX ( _table, COUNTROWS ( _table ) )
    )

Here are the results:

In the case of Argentina, in the table visual, Argentina has 12 rows, 10 of which are correct. Then the correct proportion is 10/12=83.33%. I've provided the PBIX file for this time below, and it would be great if it would be helpful to you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rsbin]

@Argha ,

My apologies, but I will not have the time to look at this in any detail for the next couple of days.

Would like to suggest you repost your original question in a new thread.  This will give another Community Member the opportunity to chime in with a solution that will work for you.

Again my apologies for not being able to bring this to a proper conclusion, but at the same time, do not want to leave you hanging.

Best Regards,