Forum Discussion

sclencioco's avatar
sclencioco
Helper I
9 years ago
Solved

multiplying columns

Hello,   Need help here.   I am trying to multiply two columns (encircled with BLUE and RED) . Unfortunately, the result shows that it used the total value on the cloumn encircled with BLUE.   ...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    Hi sclencioco,

     

    I have tested it on my local environment by using the sample data below.
    CountMonthday = CALCULATE(COUNTA(Sheet6[Date]),ALLEXCEPT(Sheet6,Sheet6[MonthName]))
    DistinctCountMonthday = CALCULATE(DISTINCTCOUNT(Sheet6[Type]),ALLEXCEPT(Sheet6,Sheet6[MonthName]))
    Measure = Sheet6[CountMonthday]*Sheet6[DistinctCountMonthday]

     

    Regards,

    Charlie Liao

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
9 years ago

Hi sclencioco,

 

I have tested it on my local environment by using the sample data below.
CountMonthday = CALCULATE(COUNTA(Sheet6[Date]),ALLEXCEPT(Sheet6,Sheet6[MonthName]))
DistinctCountMonthday = CALCULATE(DISTINCTCOUNT(Sheet6[Type]),ALLEXCEPT(Sheet6,Sheet6[MonthName]))
Measure = Sheet6[CountMonthday]*Sheet6[DistinctCountMonthday]

 

Regards,

Charlie Liao

sclencioco's avatar
sclencioco
Helper I
9 years ago

Hi Charlie_Liao

 

Got it by using your suggested solution and adjusted one of them

 

CountMonthday = CALCULATE(COUNTA(Sheet6[Date]),ALLEXCEPT(Sheet6,Sheet6[MonthName]))

 

I used this:

 

CountMonthday = CALCULATE(DISTINCTCOUNT(Sheet6[Date]),ALLEXCEPT(Sheet6,Sheet6[MonthName]))

 

 

I was able to replicate your result.

 

Thank you!