Forum Discussion

Mike91's avatar
Mike91
Icon for Helper I rankHelper I
1 year ago
Solved

distinctcount item filtered by value

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Mike91's avatar
Mike91
Icon for Helper I rankHelper I
1 year ago

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's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

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

  • Mike91's avatar
    Mike91
    Icon for Helper I rankHelper I
    1 year ago

    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's avatar
      bhanu_gautam
      Icon for Super User rankSuper User
      1 year ago

      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's avatar
        Mike91
        Icon for Helper I rankHelper I
        1 year ago

        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.