Totals by second level of a matrix's column

[TSGD2123]

Hello all,

I have a matrix like this:

ColField            ColValue1                 ColValue2                 ColValue3                TOTAL

RowFiled          Open1  Open 2         Open1  Open 2        Open1  Open 2       Open1  Open 2

RowValue1       1           3                   6           4                               3                7           10

RowValue2       2           4                   6                               2           1                10         5

RowValue3       5           2                   2           2                               2                9           13

I'm asked to include in it a Totals column at he end like in the example in red, that is,  totals by the second level of columns in the matrix, which are always the same since it's from an aux table: 

OpenedInterval =

DATATABLE(

    "Interval", STRING,

    {

        {"Open1"},

        {"Open2"}

    }

)

Thanks a lot in advance

[DataNinja777 · Accepted solution]

Hi @TSGD2123 ,

It appears the visual you want to achieve is not easily doable using one measure in the values field of the matrix table.  Even though it is not the most scalable way to do it, you can generate the visual you want by creating two separate measures for Open1 and Open2.  

I have attached an example pbix file for your reference.

Best regards, 

[v-sgandrathi]

Hi @TSGD2123

Thank you @Praful_Potphode, @rampie, @DataNinja777 , @parry2k for your responses for the query.

Has your issue been resolved?
If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.
 

Thank you for your understanding!

[Praful_Potphode · Accepted solution]

Hi @TSGD2123 

Try Creating below 2 measures and add them in the end:

Total Open1 = 
CALCULATE(
    [Your Base Measure],
    ALLEXCEPT(
        OpenedInterval,
        OpenedInterval[Interval]
    ),
    OpenedInterval[Interval] = "Open1"
)

Total Open2 = 
CALCULATE(
    [Your Base Measure],
    ALLEXCEPT(
        OpenedInterval,
        OpenedInterval[Interval]
    ),
    OpenedInterval[Interval] = "Open2"
)

When you create matrix visual below should be the fields order:

• Rows: RowField
• Columns:
  • ColField
  • OpenedInterval[Interval]
• Values:
  • Your Base Measure
  • Total Open1
  • Total Open2

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[rampie]

Hi @TSGD2123 

What you’re trying to achieve can be implemented in several ways, for example, using calculation groups or a more complex DAX measure. However, it really depends on how your data is structured.

The example you’re showing looks like the matrix visual you want to build in Power BI, but how does your raw data look, the dataset that feeds that matrix?

If you can share a sample or screenshot of the source data, I can help you write the measure

[DataNinja777 · Accepted solution]

Hi @TSGD2123 ,

It appears the visual you want to achieve is not easily doable using one measure in the values field of the matrix table.  Even though it is not the most scalable way to do it, you can generate the visual you want by creating two separate measures for Open1 and Open2.  

I have attached an example pbix file for your reference.

Best regards, 

[parry2k]

@TSGD2123 it is not clear what you are looking for. Can you provide bit more detail, sample data, and any measures/relationships used, and the end goal?

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490