distinctcount item filtered by value

[Mike91]

Hi All, i need your help to resolve this problem. 

01/02/2024	Item A	50
05/02/2024	Item B	30
10/02/2024	Item A	50
15/02/2024	Item B	20
22/02/2024	Item C	30

how i can count the number of item sales under 100?

my goal is Item < 100 = 2 and item > 100 = 1

i tried with summarize function and count.

thanks for the help

[v-jianpeng-msft · Accepted solution]

Thank you bhanu_gautam, I have the following thoughts:

Hi, @Mike91 

Based on your chat with Super User, I used the example data you provided as shown in the image below:

First, I create a calculated table using this DAX expression:

Table 2 = 
VAR _table = SUMMARIZE(
	'Table',
	'Table'[Date],
	'Table'[Item],
	'Table'[Value],
	"Month", FORMAT(
		'Table'[Date],
		"MMMM"
	),
	"Year", FORMAT(
		'Table'[Date],
		"YYYY"
	)
)
RETURN
ADDCOLUMNS(
	ADDCOLUMNS(
		SUMMARIZE(
			_table,
			'Table'[Item],
			[Month],
			[Year]
		),
		"Res", VAR _year = [Year]
		VAR _month = [Month]
		VAR _item = 'Table'[Item]
		RETURN
			SUMX(
				FILTER(
					ALL('Table'),
					FORMAT(
						'Table'[Date],
						"YYYY"
					) = _year && FORMAT(
						'Table'[Date],
						"MMMM"
					) = _month && 'Table'[Item] = _item
				),
				'Table'[Value]
			)
	),
	"IsAbove100", IF(
		[Res] > 100,
		1,
		0
	)
)

This will calculate the total values for each item for each month and determine if the total values for that month are greater than 100.

I then created two measures using the following two expressions:

Above100 = COUNTAX(FILTER(SUMMARIZE('Table 2','Table 2'[IsAbove100],'Table 2'[Item]),'Table 2'[IsAbove100]=1),'Table 2'[Item])

Below100 = COUNTAX(FILTER(SUMMARIZE('Table 2','Table 2'[IsAbove100],'Table 2'[Item]),'Table 2'[IsAbove100]=0),'Table 2'[Item])

At this point, we use the columns and months of the calculated table to create a table visual, a slicer, and a card, respectively:

When I select any month in the slicer, it calculates whether the total value of the corresponding item is greater than 100 items.

I've provided the Pbix file below.

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.

[bhanu_gautam]

@Mike91 , Create a measure to calculate the total sales for each item:

TotalSales = SUM('Table'[Sales])

Then create a measure to count the number of items with total sales under 100:

DAX
ItemsUnder100 =
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
[TotalSales] < 100
)
)

[Mike91]

i tried, but not working count a number of transaction for each item. for example in my dataset in april i sold 328 item, i calculate in excell 100 of them sold <100, with you formula i count the single transaction under 100 for each item. 

[bhanu_gautam]

@Mike91 , It is not clear can you explain in detail with example what exactly are you looking for

[Mike91]

i hope this screen can be more clear, in excell i use the pivot to calculate the total value for each item, and use a count.if to count the number of item with different condition.
with your dax i count the different transaction during the month under <100, i need count the total sales for each product during the month and count the item soddisfy my condition.

[bhanu_gautam]

@Mike91 

Load your data into Power BI.

Create a new measure to calculate the total value for each item over the month. You can use the SUMX function to achieve this. Here is an example measure:

DAX
TotalValue = SUMX(Value, Value[Value])
Create a calculated column to calculate the total value for each item over the month. This will help you to aggregate the values by item:
DAX
TotalValueByItem = CALCULATE(SUM(Value[Value]), ALLEXCEPT(Value, Value[Item]))
Create another measure to count the number of items with total values under 100 and over 100. You can use the CALCULATE and FILTER functions for this. Here are the measures:
DAX
ItemsUnder100 = CALCULATE(COUNTROWS(SUMMARIZE(Value, Value[Item], "TotalValue", SUM(Value[Value]))), FILTER(SUMMARIZE(Value, Value[Item], "TotalValue", SUM(Value[Value])), [TotalValue] < 100))
DAX
ItemsOver100 = CALCULATE(COUNTROWS(SUMMARIZE(Value, Value[Item], "TotalValue", SUM(Value[Value]))), FILTER(SUMMARIZE(Value, Value[Item], "TotalValue", SUM(Value[Value])), [TotalValue] > 100))
Here is a step-by-step breakdown:

Load Data: Import your data into Power BI.

Create Total Value Measure:

Go to the "Modeling" tab.
Click on "New Measure".
Enter the following DAX formula:
DAX
TotalValue = SUMX(Value, Value[Value])
Create Total Value By Item Calculated Column:
Go to the "Modeling" tab.
Click on "New Column".
Enter the following DAX formula:
DAX
TotalValueByItem = CALCULATE(SUM(Value[Value]), ALLEXCEPT(Value, Value[Item]))
Create Items Under 100 Measure:
Go to the "Modeling" tab.
Click on "New Measure".
Enter the following DAX formula:
DAX
ItemsUnder100 = CALCULATE(COUNTROWS(SUMMARIZE(Value, Value[Item], "TotalValue", SUM(Value[Value]))), FILTER(SUMMARIZE(Value, Value[Item], "TotalValue", SUM(Value[Value])), [TotalValue] < 100))
Create Items Over 100 Measure:
Go to the "Modeling" tab.
Click on "New Measure".
Enter the following DAX formula:
DAX
ItemsOver100 = CALCULATE(COUNTROWS(SUMMARIZE(Value, Value[Item], "TotalValue", SUM(Value[Value]))), FILTER(SUMMARIZE(Value, Value[Item], "TotalValue", SUM(Value[Value])), [TotalValue] > 100))
Add Measures to Report: Drag and drop these measures into your report to visualize the counts.
This will give you the count of items with total values under 100 and over 100 as per your requirement.

PBIX attached

[Mike91]

I follow all step, but not working, count only 40  item when the item under 100 are more. i can't understand why more sum of value under 100 not counted.

[bhanu_gautam]

@Mike91 , Did you checked the attached PBIX file

[Mike91]

Yes, i use them to start, only difference it's my dataset period is current fyscal year and i need do this count each month, this is my result for april