cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Measure to calculate SUM of Values based on Grouping of columns from two Tables

Hello I am new to Power BI and would appreciate help regarding the following:

 

Given:

Year  Month  Day  Sales

2010     1         1    300

2010     1         2    100

2010     2         1    300

2010     2         2    200

 

 

Expected

Year   Month  Day   Summation of Sales By Month

2010     1         1             400

2010     1         2             400

2010     2         1             500

2010     2         2             500

 

Constraints:

I am using DirectQuery

In this example the sorting I require Summation by Month

What I actually require is Summation by Month and Age

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Measure to calculate SUM of Values based on Grouping of columns from two Tables

Hi @meetgandhi100 ,

 

Capture.PNG


Did I answer your question? Mark my post as a solution!
Ricardo

View solution in original post

Highlighted
Community Support
Community Support

Re: Measure to calculate SUM of Values based on Grouping of columns from two Tables

Hi,

 

Please try this measure:

Sum = CALCULATE(SUM('Fact Table'[Sum]),FILTER('Fact Table',RELATED('Dimension 2 Table'[Category])="A"))

The result shows:

3.PNG

See my attached pbix file.

 

Best Regards,

Giotto

View solution in original post

10 REPLIES 10
Highlighted
Community Champion
Community Champion

Re: Measure to calculate SUM of Values based on Grouping of columns from two Tables

Hi @meetgandhi100 ,

 

Capture.PNG


Did I answer your question? Mark my post as a solution!
Ricardo

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Measure to calculate SUM of Values based on Grouping of columns from two Tables

Hi @meetgandhi100 

 

Try this

Measure = 
CALCULATE(
    [Sales],
    ALL( 'Calendar'[Day] )
)
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn




Highlighted
Community Support
Community Support

Re: Measure to calculate SUM of Values based on Grouping of columns from two Tables

Hi,

 

Please try this measure:

Summation of Sales By Month = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Month] IN FILTERS ( 'Table'[Month] ) )
)

The result shows:

15.PNG

 

Best Regards,

Giotto

Highlighted
Frequent Visitor

Re: Measure to calculate SUM of Values based on Grouping of columns from two Tables

Thanks @camargos88  . What If I want to Group based on two columns from two different tables?

 

Highlighted
Community Champion
Community Champion

Re: Measure to calculate SUM of Values based on Grouping of columns from two Tables

Hi @meetgandhi100 ,

 

It depends on how they are related. But it's possible.


Did I answer your question? Mark my post as a solution!
Ricardo

Highlighted
Frequent Visitor

Re: Measure to calculate SUM of Values based on Grouping of columns from two Tables

Great, can you help me with the exact situation.

 

Dimension 1 Table                  DImension 2 Table                          Fact table

Year   Month   Key1                Category    Catkey                          Key1  Catkey  Sum

2010    Jan           1                        A               1                                 1       1         100

2011    Feb          2                         A               2                                1       2          100

                                                      B               3                                 1       3          100

 

Required Output

 

Year  Category   Sum

2010    A             200

 

Can you let me know how to create a measure to acheive this using with DirectQuery @camargos88 @v-gizhi-msft @Mariusz 

Highlighted
Community Champion
Community Champion

Re: Measure to calculate SUM of Values based on Grouping of columns from two Tables

Hi @meetgandhi100 ,

 

I believe if you have them related, you just need to drag the year / category and sum columns to the tables visual. Also filter the category using a slicer.

 

Capture.PNG


Did I answer your question? Mark my post as a solution!
Ricardo

Highlighted
Frequent Visitor

Re: Measure to calculate SUM of Values based on Grouping of columns from two Tables

I just want them to be shown in a report , wihout any slicers. 

Highlighted
Community Champion
Community Champion

Re: Measure to calculate SUM of Values based on Grouping of columns from two Tables

@meetgandhi100 ,

 

You can use the filter pane.

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-report-filter

 

Just click on the visual and select the filters.

Also you can apply for the page or report.


Did I answer your question? Mark my post as a solution!
Ricardo

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors