Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Dario87
Regular Visitor

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 column and not for the row (it is calculated by summarizing the invoice amounts, that are per row).
I need this to make a comparison between every incomes and revenues of the period for every aging range. So, the result that I want to get is the same revenue value per each row filtered only by invoice date (the column). After that, I have created another measure which is the comparison % between the row incomes and the calculated revenue.
I have tried with:
Revenue = CALCULATE (SUM (Payments [Invoice Amount]); ALLSELECTED (Payments)), but I get the result in the screenshot
Revenue = CALCULATE (SUM (Payments [Invoice Amount]); ALLEXCEPT (Payments; Payments [Invoice Date])), but I've obtained the same result.

Do you have any suggestions?

 

Visual configuration

  • Type: Matrix
  • Rows: Aging (string)
  • Columns: Invoice Date (hierarchical date)
  • Values:
    • Importo incasso --> Incomes (currency)
    • Fatturato --> Revenue (currency measure)
    • % Incassato --> Income on revenue (% measure)

 

This is what I've have obtained:

IncassoSuFatturato.png

 

Thanks to everybody

1 ACCEPTED SOLUTION
Dario87
Regular Visitor

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,

View solution in original post

6 REPLIES 6
Dario87
Regular Visitor

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
Regular Visitor

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.
amitchandak
Super User
Super User

@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.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Fatturato_2.png

 

Let me know if you need any other information.

 

Regards,

@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.

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,

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.