Forum Discussion
Helper VSUM with filter
Hey guys!
I want a chart where i can sum all my sold fruits. If i want to summarice for example Pear i need to scann thruu all artikels and if artikel = Pear and need to get the Amount of the same number.
Can someone help how i can calculate this in a measure?
I was trying something like this but it didnt work.
Total sum PEAR= CALCUTE(SUM(Amount1)FILTER(MyTable,MyTable[Artikel1]=Pear))+CALCUTE(SUM(Amount2)FILTER(MyTable,MyTable[Artikel2]=Pear))+CALCUTE(SUM(Amount3)FILTER(MyTable,MyTable[Artikel3]=Pear))+CALCUTE(SUM(Amount4)FILTER(MyTable,MyTable[Artikel4]=Pear))Jensej , You may to unpivot multiple time and check if this can help
Hi Jensej,
Dax formula not suitable to handle this type of table structure to calculate across multiple fields, it required you to manually add conditions on all the value fields.
In my opinion, I'd like to suggest you enter to query editor, duplicate raw query and do 'unpivot columns' on the copied query table value fields. Then you can simply write formulas to calculated through these value fields.
Regards,
Xiaoxin Sheng
3 Replies
ryan_mayuSuper User
I use the same method and get the correct result.What's your expected result?
Measure = CALCULATE(sum('Table'[amount1]),FILTER('Table','Table'[ARTIKEL 1]="Pear"))+CALCULATE(SUM('Table'[amount2]),FILTER('Table','Table'[ARTIKEL 2]="Pear"))+CALCULATE(sum('Table'[amount3]),FILTER('Table','Table'[ARTIKEL 3]="Pear"))+CALCULATE(sum('Table'[amount4]),FILTER('Table','Table'[ARTIKEL 4]="Pear"))btw,your data layout is not good. I suggest you change to something like below.
- amitchandak
Jensej , You may to unpivot multiple time and check if this can help
Hi Jensej,
Dax formula not suitable to handle this type of table structure to calculate across multiple fields, it required you to manually add conditions on all the value fields.
In my opinion, I'd like to suggest you enter to query editor, duplicate raw query and do 'unpivot columns' on the copied query table value fields. Then you can simply write formulas to calculated through these value fields.
Regards,
Xiaoxin Sheng