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,
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Dario87 , As revenue might not have age and might work with invoice date, I would like see how the data is placed

Can you share sample data and sample output.

Dario87's avatar
Dario87
Regular Visitor
6 years ago

Hi amitchandak ,

thank you for your fast reply.

 

Here some sample rows of my dataset:

IdDelay DaysAgingInvoice Date (yyyy-MM-dd)Invoice AmountIncome
12<=302018-01-2350.0050.00
24530 <= 602017-07-0334.0034.00
37860 <= 902019-04-2112.0012.00
45<= 302020-05-0345.00 
50<= 302017-09-0925.0025.00

 

And what I expect from my real table is to have 277,478,993.23 for each row of Fatturato 2017, 345,705,749.02 for each row of Fatturato 2018, ... including the total row

 

Here the result using the simple data set data

 

Let me know if you need any other information.

 

Regards,

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Dario87 ,

     

    I would suggest you create another date table named 'Calendar' and make relationship between 'Calendar' and 'Payments' table, then create slicer based on the date column in 'Calendar' table and create a measure using dax as below:

    Revenue =
CALCULATE (
    SUM ( Payments[Invoice Amount] );
    FILTER (
        ALLEXCEPT ( 'Payments'; 'Payments'[Aging] );
        'Payments'[Date] IN VALUES ( 'Calendar'[Date] )
    )
)

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Dario87's avatar
      Dario87
      Regular Visitor
      6 years ago

      Hi v-yuta-msft ,

      thank you for your reply.

      Anyway I don't think that your solution fits my goal.

      You suggest to use a slicer, but my purpose is to visualize (not filter) the revenues per column value (Invoice Date).

      So I won't use a slicer.

      Regards,