Count and sum unique Values

[skorpion]

Im trying to get the sum of Sales with uniuqe Catatery im using thus DAX Formula please anyone can help me in this

Countoracle = CALCULATE(SUM('Data Source'[Sales) ,DISTINCTCOUNT ('Data Source'[ItemAr]))

getting error 

The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

[BalaVenuGopal]

Hi @skorpion ,

Could you please explain indetaled .

Assume you have the data like bellow

Category Value

A               100

A                200

B                50

B                60

Do you want to sum value for each category or do you  need max value from each category what is your requirement ??

Please explain in detailed.

If you need Sum of value for each category .

Create measure M1 = CALCUALTE(SUM(Value)) and then drag this measure and Category in your report will work .

Reply me if you need further assistance.

Regards,

Venu Gopal

[skorpion]

Looking for sum value for each category

[vanessafvg]

@skorpion place the category on your visual, and then create the sum measure and put that on your visual and you will then seen what you asking for

[vanessafvg]

@skorpion  why are you combining 2 different measures?

this should give you the total sum

SumofSales= CALCULATE(SUM('Data Source'[Sales) )

Countoracle = CALCULATE(DISTINCTCOUNT ('Data Source'[ItemAr])

what result are you expecting?, if you create them separately then place them on your visual you can still see them together.

please give an example of what you hope to achieve

[skorpion]

yea but when i sum value it will sum all Cataery which repeat and have twice so i need Uniqe sum 

[vanessafvg]

@skorpion please provide an example of what you expecting as i am unclear what you trying to do

are you wanting to count the total products, total categories, total sales value

provide an example of what it should look like

[skorpion]

this is the Distinct Formula for Item

CountDataset = CALCULATE (
DISTINCTCOUNT ('Data Source'[ItemAr]))

which working Fine

Now there is one more Column where we mention 1 = Yes and 0 = No

now in Excel column Item have muti entries and Column Sale have 1 and 0

so i need in Power BI sum All Unique Sale Item with this CountDataset = CALCULATE (
DISTINCTCOUNT ('Data Source'[ItemAr]))

[vanessafvg]

@skorpion ill be honest you still not explaining very well, whats helpful is to show me some of your data and then what result you expect?   trying to help wtih all the information helps

if i understand what you saying you only want unique products?  unique products is 1 or 0?    data will really help here

CALCULATE (
DISTINCTCOUNT ('Data Source'[ItemAr]), columnsale = 0)

that will give you the total count of all products that  that have 0 columnsale

[skorpion]

Item have cat and Sub cat sa well 

Countorcale = CALCULATE (
DISTINCTCOUNT ('Data Source'[ItemAr]), DISTINCTCOUNT('Data Source'[CatAr]),DISTINCTCOUNT('Data Source'[SubAr]),'Data Source'[Orcale] = 1)

[vanessafvg]

@skorpion please provide the sub categorry below too with the expected result

Juice   1

Water 1

Juice  0

Water 1

Juice   1

Water 1

Output 

juice     2

Water   3

[skorpion]

solved thanks your help

[vanessafvg]

@skorpion post the solution and make it the solution so others can learn from it 

[skorpion]

Its very hard to explain

For exampple

Juice   1

Water 1

Juice  0

Water 1

Juice   1

Water 1

Output 

juice     2

Water   3