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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Guitarseb
Frequent Visitor

Sum all value of one group keeping the filter of another table

Hello I have these 2 simple table

Tabella (2)

tab.gif

 

Calendario

calendar.gifI would like to have report a table with one column that show the sum of all the value of the group "a" on all the row but only for the selected period

So If I filter only the year 2001-2003 I want all the row with the value 100. If I filter all the year I want all the row with the value 500 (100+400) like below

filter 1.gif

 

filter 2.gif

I have tryed some formulas like the below but noone of them give me the expected result

 

sum a value = calculate(sum('Tabella (2)'[valore]),CALCULATETABLE(ALLEXCEPT('Tabella (2)',calendario[anno]),'Tabella (2)'[Gruppo]="a"))
 
sum a value = calculate(sum('Tabella (2)'[valore]),ALLEXCEPT('Tabella (2)',calendario[anno]),'Tabella (2)'[Gruppo]="a")
 
sum a value = calculate(sum('Tabella (2)'[valore]),ALL('Tabella (2)'),'Tabella (2)'[Gruppo]="a")
 
The all function is deleting the filter on the year, and allexcept is not making the sum of all the "a" of the selected period
 
Thanks In advance
 

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@Guitarseb 

I am not sure what results you need at grand total level, check this measure please:

sum a value = 
CALCULATE(
    SUM(Tabella2[valore]),
    Tabella2[Gruppo] = "a",
    ALLSELECTED(Tabella2)
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

4 REPLIES 4
nikos_derv
Frequent Visitor

Hello, i am trying to do the same thing here, but i can't fugure out how.. did you found a working solution eventually? 

Fowmy
Super User
Super User

@Guitarseb 

I am not sure what results you need at grand total level, check this measure please:

sum a value = 
CALCULATE(
    SUM(Tabella2[valore]),
    Tabella2[Gruppo] = "a",
    ALLSELECTED(Tabella2)
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi Funny, i found the problem. I was writing the formula in a new column instead of a new measure. So now your formula is working.

Thanks

No i'm sorry but I still get the same result. When I filter with the year the total is not filtered. I would like to filter all the data like the Subtotal at the end of the table and then filter only the group="a". This is the result I get with your formula: all 500 that is the sum of the value of the 2001 and 2006 (100+400)

tab33.gif

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors