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.
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!
Could you please share sample data of your orginal table and post expected result following the guide in this blog:
Here's the link to the excel file with sample data an all explanations to the issue.
Thanks in advance.
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.
Thanks for your time,
Here's the link for thr PBIX file (all explanations are inside the model).
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:
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.
Frankly I suggest you start again.
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.
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.
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.
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.
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.
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.