Forum Discussion

Dario87's avatar
Dario87
Regular Visitor
6 years ago
Solved

Calculated value filtering on column in matrix

Hi everybody, Basically I have to make a comparison between incomes and revenues. The column that I am trying to calculate through a measure is revenues. I need the value filtered only for the col...
Need Help
  • Dario87's avatar
    Dario87
    6 years ago

    I've found the solution:

    Revenue = CALCULATE(SUM(Payments[Invoice Amount]); ALLEXCEPT(Payments; Payments[Invoice Date].[Year]; Payments[Invoice Date].[Quarter]; Payments[Invoice Date].[Month]; Payments[Invoice Date].[Day]; Payments[Company]))
     
    With that also drillthrough works properly
     
    Regards,
Dario87's avatar
Dario87
Regular Visitor
6 years ago

I've (near) obtained the required result by creating this misure:

Revenue = CALCULATE(SUM(Payments [Invoice Amount]); ALLEXCEPT(Payments; Payments [Invoice Date].[Year]))
 
But if I visualize the grid with column Payments [Invoice Date] at year level, it works properly, when I drill down by column, the Revenue values are bound to [Invoice Date].[Year] so the value doesn't change dynamically.
I've tried to set [Date] to ALLEXCEPT but seems that it doesn't have effects.