CODEWITHSUNDEEP
powerbidaxcalculated-columnspowerbi-desktop
sara
sara

Reputation: 37

Powerbi Calculate column to return totals for each category only once

I will do my best to explain this correctly:

I have a list - by store, by fruit type. I need to create a new column where I have one line of total by store and by fruit (yellow column). In Excel, I can do a formula that does that but I cannot figure out the Dax formula. Or is there another way to do it? Maybe a new table and link them? 

result in yellow

Upvotes: 0

Views: 2331

Answers (3)

sara
sara

Reputation: 37

I figured it out:

2 = CALCULATE( SumX( FILTER( SUMMARIZE(....,"Aggregate", SUM(....])), [Aggregate]>=2 ), [Aggregate] ),

)

Upvotes: 1

varnhem
varnhem

Reputation: 63

Not the most elegant way of dealing with it, I'll try to keep thinking of alternate solutions... but a possibility would be to create a measure for the different permutations

m1 = IF(CALCULATE(COUNT(Sheet1[Fruit]), FILTER(Sheet1,[Store #] = "Store 1"),Sheet1[Fruit]="apple")>2,CALCULATE(COUNT(Sheet1[Fruit]), FILTER(Sheet1,[Store #] = "Store 1"),Sheet1[Fruit]="apple"), 
IF(CALCULATE(COUNT(Sheet1[Fruit]), FILTER(Sheet1,[Store #] = "Store 1"),Sheet1[Fruit]="apple")<2,CALCULATE(COUNT(Sheet1[Fruit]), FILTER(Sheet1,[Store #] = "Store 1"),Sheet1[Fruit]="apple"),0))

Out of curiosity, how did you deal with it in excel?

Upvotes: 0

varnhem
varnhem

Reputation: 63

PowerBI will aggregate these values for you in a visual. For instance, in a table if you drag the Store # attribute, and the Fruit attribute to a viz:

Example

Upvotes: 0

Related Questions