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

[meetgandhi100]

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

[v-gizhi-msft]

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:

Best Regards,

Giotto

[Mariusz]

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

[camargos88 · Accepted solution]

Hi @meetgandhi100 ,

[meetgandhi100]

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

[camargos88]

Hi @meetgandhi100 ,

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

[meetgandhi100]

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 

[v-gizhi-msft]

Hi,

Please try this measure:

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

The result shows:

See my attached pbix file.

Best Regards,

Giotto

[camargos88]

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.

[meetgandhi100]

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

[camargos88]

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