MATRIX GrandTotal calculation with TOPN multiples dimensions in same column (measure)

[JosePintor]

Hi everybody,

I have a similar issue with calculate GrandTotal over a TOPN list that changes with drilldown of 4 dimensions.

how can i SUM only the visibles TOP5 values refering to the correspondent dimension at visible drilldown level.

Let's explain:

I have a matrix with 4 dimensions with drilldown (TipoArtigo; Marca; Familia; Artigo)

I want to put the totals in the middle column (wich is a measure of TOPN margin of the dimension that is in each level of drill down. Basically I need to have only one measure that works for all 4 drill downs dimensions.

I tried with SUMX and TOPN to achive Totals only for TOPN of each dimension, but I couldn’t be able to achieve that. The problem is that in totals we don’t have row context and don’t know what dimension is in matrix lines at each moment, to sum only TOPN (5 in case) for that dimension.

I have 4 measures (one for each dimension) that work ok only when matches with the dimension in drilldown, that's why I need a meaures for all dimensions.

1st column (TOPN HierarArtigo Margem) works ok in all dimensionsm but give the GrandTotal fo all the items and not only the visibles ones (TOP 5).

2nd column column (TOPN HierarArtigo Margem Parcial)  is the one o don’t work on totals only for TOP 5 itens.

3rd  column  (TOPN <dimension _name> Margem Parcial) work ok only with the corresponding dimension in lines otherwise gives only total (and wrong, because it’s the total that TOPN is supposed to calc based on the specific data_column).

In the follows print screens the 1st and 2nd measures area the same for all drilldowns, the 3rd changes….

Don’t worried because the grand total of the first column is lower than the parcial totals, because there are items with negative values (margin).

At the end I post all those measures.

How can I SUMX just the visibles rows in matrix depending on drilldown level selected by user, that changes the TOPN ( VALUES ( column) ) ?!

Thanks for help!

Regards,

José Pintor

[v-yuezhe-msft]

@JosePintor,

Could you please share sample data of your orginal table and  post expected result following the guide in this blog:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490?

Regards,
Lydia

[JosePintor]

Hi Lydia,

Here's the link to the excel file with sample data an all explanations to the issue.

Sample Data

Thanks in advance.

Jose Pintor

[MattAllington]

Jose

You need to post a PBIX file that contains the problem, otherwise those that are trying to help you have to first rebuild the workbook to re-create the problem.  Please post a sample PBIX that generates the problem as you have described.

[JosePintor]

Hi Matt,

Thanks for your time,

Here's the link for thr PBIX file (all explanations are inside the model).

https://www.dropbox.com/s/4xgcpk3g694z1tn/Test.pbix?dl=0

Best Regards

José Pintor

[MattAllington]

Hi Jose

I know you are trying hard to get help here, but I can't really start to help you with your DAX issues with the data model you have got.  This is the main model (for the benefit of others reading along)

In addtion there are 15 other tables that are not joined to the main model (not shown above to save space). 

This is not a good design for Power BI and looks to me like you have just loaded what is in your ERP system.  This is not what you should be doing.   Some questions I have for you:

1. why do you have a many to many relationship between RLSAplicacao and Movimentos?
2. and also between TKPIMestreV and TKPIMestre?
3. what is the purpose of the inactive relationship between CalendBase and TKPIObjMensal?
4. what is the purpose of the 15 tables not joined to the main model?
5. there are around 175 columns in the Movimentos.  What is the purpose of each of these columns in your report?

If you don't know the answers to these questions, then you haven't completed the first part of the data modelling process.

The way you should start the process is to look at the data table you need (presumably Movements in this case) and start from there. 

1. The purpose of columns in the data table are either to show transacitonal information or to provide a unique ID to a dimension (Lookup table)
2. The purpose of lookup tables is to provide more information about each transaction using the unique ID column
3. The purpose of relationships in Power BI is to propagate filters between the lookup tables and the data tables

Frankly I suggest you start again. 

• Read my article about how to load and structure tables in Power BI
• Start with the movements table and think about what you want to report on.  Load just the columns you need in the report
• Ask youself "who, what, when, where"  about each transaction in the table (these are the dimensions - there may be more)
• Make sure there is an ID column that uniquely identifies "who, what, when, where" 
• Load a look up table for each ID column.  Where possible consolidate other source tables into a single lookup table for each dimension

The bottom line is a Power BI reporting database is not the same as a transactional database.  If you simply load your transactional tables into Power BI you will be in for a world of pain.

Sorry to be the bearer of bad news.

Matt

[JosePintor]

Hi Matt,

Thanks for your answer.

I have wrote a detail text in the last hour answering to all your questions, but when i was posting it, the browser breaked and gave me an error and i lost all the text .

if you really need it i you explain all in detail tomorrow... (here is midnight...)

But i (re)publish the same model (same link) with only 6 tables (3 are parameters tables) and now i think it's easy for you to take a look into this issue.

Let me now please.

José Pintor

[MattAllington]

Like I said, I know you are working hard to try to get a solution here, but I really can't help you any more.  The issues preventing me helping you are.

1. The model is not in English and hence I can't put context onto any of the measures, columns, tables
2. You have hundreds of columns in your data table making peformance unworkable.
3. You have hundreds of measures (600 actually), many that are written in a way that I would not write and I don't understand and are not best practice.  eg. look at this small list of measures below

This small list of hard coded measures refers to 12 different columns in the fact table (and the list is not complete).  It would likely be better to unpivot the columns into an Attribute and Value column and then use the attribute to control the output.  

Then you have measures that I don't understand becuase of the language difference and also the logic that has been included. eg this one.

=
VAR ALLColumns =
    ALL (
        Movimentos[TipoArtigo],
        Movimentos[TipoMarca],
        Movimentos[Marca],
        Movimentos[Familia],
        Movimentos[SubFamilia],
        Movimentos[Artigo],
        Movimentos[HierarqComNiv1Nome],
        Movimentos[HierarqComNiv2Nome]
    )
RETURN
    CALCULATE (
        [Margem Tot],
        FILTER (
            ADDCOLUMNS (
                VALUES ( Movimentos[TerceiroID] ),
                ""Valor Hist"", CALCULATE (
                    COUNTROWS ( Movimentos ),
                    ALLColumns,
                    FILTER ( ALL ( CalendBase ), CalendBase[Ano] < MIN ( CalendBase[Ano] ) ),
                    Movimentos[VlrBrutoTotal] > 0
                )
            ),
            [Valor Hist] = 0
                && CALCULATE ( [Valor Tot], ALLColumns, VALUES ( Movimentos[Terceiro] ) ) > 0
        )
    )

For these reasons, I can't help you write the DAX formula that you are after.  From experience, I think that if you fix the issues I have outlined, you won't need my help to write the DAX formula.

[JosePintor]

Hi Matt,

I’m really working hard to achieve the solution.

Thanks for your patience, because I understand the difficult you talk.

About the measures you posted, I explain: they exist to control within several measures (RLS – Row Level Security) in Power BI Desktop depending on active USERNAME (local or Tenant). As you know only in Service RLS is native of Power BI and can be used directly.

To this sample model works with your USERNAME (local User PC or Tenant) i changed the tests and you see VAR _Valor = 1 to pass the control OK for all the other measures.

Only to explain better, the original Model have all this complexity because, it has more than 40 dimensions, more than 100 reports pages and +- 300 visuals /dashboards (is based on Balanced Scorecard, and presents all kind of data in an multi-company environment  (FINANCES; COSTUMERS, INTERNAL PROCESSES, LEARNING & GROWTH) and hundreds of measures to show all differents KPI’s etc…

Just one more curiosity: the source ERP have more than 5000 tables! Therefore exists an ETL in SQLServer to normalize/combine all data in one datatable, otherwise we’ll have in this Power BI model much…much more tables.

And it works ok as is!

Well, forget all above!

To put all confusion away, I create a model with the same values, but only 10 columns in datatable (now called SalesTable) and have only a few measures related with this issue (TOPN Product Hierarchy -4 levels)

Now the model is simple and clear and can’t make no confusion. (the link is the same).

Can you take a look please.

Regards,

José Pintor

[JosePintor]

Hi Matt,

Did you have the opportunity to see the last pbix sample file that i published with the link in the previous post.

Thanks for reply.

Regards

José Pintor