Problems with a calculated table

[miikasa]

I have created a calculated table in Power BI that shows the information above:

Period; Index [which are some measures, one for each period, like: Sales2023, SalesJanuary2024, etc]; Area

The problem is, no matter the area, I'm getting the same values to the same period, like this:

January/2024: 1,5%, Finance
January/2024: 1,5%, IT

Any ideas to solve this? Thank you in advance.

[miikasa]

Hey, thank you so much for your answer. I tried what you said, I'm not sure why it didn't work. There is still a problem when it comes to calculate the index to each area. 

What I want is to create a calculated column as below:

Period                       Value                 Area

Accumulated 2023   [AccSales2023]  Finance

January 2024            [SalesJan2024]  Finance

February 2024          [SalesFeb2024]  Finance

Accumulated 2023   [SalesJan2024]     IT

January 2024            [SalesJan2024]    IT

February 2024          [SalesFeb2024]   IT

So, I'll share the measures I created, maybe it could be helpful for you to understand.

I have 4 tables: Sales,BoardSales, Calendar, Areas

These are my measures:

SalesNumber = SUM(Sales[SalesAmount])

MonthlySum=

CALCULATE( SUM('BoardSales'[TOTAL]),

ALLEXCEPT('BoardSales', 'BoardSales'[Month-Year]) )

PreviousMonthSum =

CALCULATE( [MonthlySum], PREVIOUSMONTH('BoardSales'[Month-Year]) )

AverageCurrentMonthvsPreviousMonth =

IF( ISBLANK([PreviousMonth]),

BLANK(),

([MonthlySum] + [PreviousMonth]) / 2 )

SalesIndex=

DIVIDE(

    [SalesNumber],

    [AverageCurrentMonthvsPreviousMonth],

    0

)

What I need to demonstrate in the calculated table:

AccSales2023=

CALCULATE(

    SUMX(

        VALUES('Calendar'[Month-Year]),

        [SalesIndex]

    ),

    'Calendar'[Year] = 2023

)

SalesJanuary2024=

CALCULATE(

[SalesIndex],
'Calendar'[Date] >= DATE(2024, 1, 1) && 'Calendar'[Date] <= DATE(2024, 1, 31),
ALL('Calendar'),
ALLSELECTED(Areas[AREAS])

)

Hope it can be helpful and thank you again!

[ryan_mayu]

could you pls provide some sample data and expected output?

[miikasa]

Hi there, thank you for your message.

What I want is to create a calculated column as below:

Period                       Value                 Area

Accumulated 2023   [AccSales2023]  Finance

January 2024            [SalesJan2024]  Finance

February 2024          [SalesFeb2024]  Finance

Accumulated 2023   [SalesJan2024]     IT

January 2024            [SalesJan2024]    IT

February 2024          [SalesFeb2024]   IT

So, I'll share the measures I created, maybe it could be helpful for you to understand.

I have 4 tables: Sales,BoardSales, Calendar, Areas

These are my measures:

SalesNumber = SUM(Sales[SalesAmount])

MonthlySum=

CALCULATE( SUM('BoardSales'[TOTAL]),

ALLEXCEPT('BoardSales', 'BoardSales'[Month-Year]) )

PreviousMonthSum =

CALCULATE( [MonthlySum], PREVIOUSMONTH('BoardSales'[Month-Year]) )

AverageCurrentMonthvsPreviousMonth =

IF( ISBLANK([PreviousMonth]),

BLANK(),

([MonthlySum] + [PreviousMonth]) / 2 )

SalesIndex=

DIVIDE(

    [SalesNumber],

    [AverageCurrentMonthvsPreviousMonth],

    0

)

What I need to demonstrate in the calculated table:

AccSales2023=

CALCULATE(

    SUMX(

        VALUES('Calendar'[Month-Year]),

        [SalesIndex]

    ),

    'Calendar'[Year] = 2023

)

SalesJanuary2024=

CALCULATE(

[SalesIndex],
'Calendar'[Date] >= DATE(2024, 1, 1) && 'Calendar'[Date] <= DATE(2024, 1, 31),
ALL('Calendar'),
ALLSELECTED(Areas[AREAS])

)

Hope it can be helpful and thank you!

[Shravan133 · Accepted solution]

Hi @miikasa , Not a lot of information to work with. maybe provide us screenshots of your table and columns?

anyway try this:

To address the issue where the same values are being shown for different areas in your calculated table, we need to ensure that the measures you're using are being correctly filtered by the Area context. This involves making sure that your measures account for the Area context in their calculations.

Here’s a general approach to troubleshoot and solve this issue:

1. Ensure Measures are Correctly Filtered by Area: Verify that your measures are written in a way that they consider the Area filter. If your measure does not inherently filter by Area, you may need to adjust it.
2. Use CALCULATE with FILTER Context: If necessary, wrap your measures in CALCULATE and explicitly apply the filter context using FILTER.
3. Review Relationships: Ensure that the relationships between your tables are set correctly in the data model. The Area field should be properly related to the relevant data tables.

Example:

Assuming you have a measure Sales2023 and you want it to be correctly filtered by Area, here is how you might adjust your measure:

Sales2023 =

CALCULATE(

    SUM(SalesTable[SalesAmount]),

    FILTER(

        SalesTable,

        SalesTable[Year] = 2023

    )

)

If the Area is not being considered, you can make sure it is included in the calculation:

Sales2023 =

CALCULATE(

    SUM(SalesTable[SalesAmount]),

    SalesTable[Year] = 2023,

    SalesTable[Area] = SELECTEDVALUE(AreaTable[Area])

)

Creating the Calculated Table:

When creating the calculated table, make sure that you include the Area in the context of the measures. Here’s an example of how to create a calculated table that correctly filters by Area:

CalculatedTable =

ADDCOLUMNS(

    GENERATE(

        ALL(PeriodTable[Period]),

        VALUES(AreaTable[Area])

    ),

    "Index",

    SWITCH(

        TRUE(),

        [Period] = "2023", [Sales2023],

        [Period] = "January2024", [SalesJanuary2024],

        ...

    )

)

In this example:

• ALL(PeriodTable[Period]) ensures that all periods are considered.
• VALUES(AreaTable[Area]) ensures that each area is considered for each period.
• The SWITCH function assigns the correct measure based on the period.

Example Data Model:

Consider a data model where SalesTable contains sales data, PeriodTable contains period information, and AreaTable contains area information. Ensure that there are relationships set up correctly between these tables, especially connecting SalesTable to AreaTable through an AreaID or similar field.

Verification:

1. Check Data Model: Verify relationships in the model view.
2. Test Measures: Test each measure in a card visual or table visual to ensure they are correctly filtered by Area.
3. Create Sample Table: Create a simple table visual with Period, Area, and your measure to ensure they display correctly before adding complexity.

By ensuring that your measures are correctly filtered by Area and that your calculated table respects these filters, you should be able to resolve the issue where the same values appear for different areas.

[miikasa]

Hi there, I tried again and it worked! I'll just confirm the values, but now the filters of areas is working.

I can't thank you enough. 

Warm regards.