Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

How to multiply with filters

Hello,

I have problems with the DAX formula for multiplying numbers from my fact table with filters.

I would like to multiply the category A with B, but also only multiply each subcategory with the same subcategory from the different category.

Example

(A - first) 2,5 × (B - first) 187 =

(A - second) 3 × (B - second) 99 =

I want to leave out C completely.

I have three tables. 1 fact table with the Amount, 2nd is the dimension table Category and the last one is dimension table Subcategory.

How to go around this problem with a DAX formula? Is it even possible?

Thank you so much

Anonymous
4 years ago
In the end I just created two variables with filters and multiplied them

Measure =
var acat = CALCULATE(SUM('Table'[Amount]),Category[Category]="a")
var bcat = CALCULATE(SUM('Table'[Amount]),Category[Category]="b")
return acat*bcat

Not ideal but it does the work.

3 Replies

amitchandak
Super User
4 years ago
Anonymous , Try a measure like

calculate(ProductX(values(Subcategory[Subcategory]), calculate(Sum(Fact[Amount])), filter(Category,Category[Category] <>"C")) )
Anonymous
Not applicable
4 years ago
Its showing me error because of the two brackets after Fact[Amount]. The numbers dont match. After i tried the measure, it shows completely different numbers. I want to display the subcategory with the calculated amount.
I want to put this measure into a new table and there will be two columns with the subcategories and the calculated column
First | 467.5
Second | 297
Third | 682.5
Something like this
Anonymous
Not applicable
4 years ago
In the end I just created two variables with filters and multiplied them

Measure =
var acat = CALCULATE(SUM('Table'[Amount]),Category[Category]="a")
var bcat = CALCULATE(SUM('Table'[Amount]),Category[Category]="b")
return acat*bcat

Not ideal but it does the work.