DAX - Help required with this complicated requirement (warning advanced)

[Anonymous]

Hi,

(First off, the data is ficticious).

I have been given the task of replicating an Excel report in Power BI. Due to Excel being cell based I find I am having to create a measure to cater for each individual Excel cell. This is working, however I am having to create about 80 measures to do so but the real problem comes when aligning each measure, in it's own table visual cell, to look like a well presented table. Quite frankly, this is taking a very long time to do, and when I need to move something on the page then all has to be re-alligned again (head in heads).

I would like to know if there is a way, using DAX (although I would accept it in Power Query if not doable in DAX), to create a table on the fly, containing all the individual measures (the actual measure's code rather than referencing the measure itself).

Maybe if I was to you show you my starting point it may become clearer.

As you can see nothing too daulty here. The end point in a Power BI matrix visual is below.

You can see the rows for Sales, SSGP.... Gross Profit is nicely aligned as it sits within a matrix visual. Grouping Level being the Rows, Division being the Columns. Each row for SSM, FCP, Cust Rebate, Sup Rabate, and FA GM is it's own table row. Each cell in each row is a measure. It is these measures that I've had to create to replicate the original Excel cell formulas. It is a complete nightmare to line-up and any movement to the page down-the-line will entail the re-alignment of all rows/measures again.

And quite frankly, I wonder if creating each individual measure is the most optimal way of doing this.

I'll show you three measures at random so you are able to see what sort I thing I will need to incorporate on the fly.

Measure for SSM - North - DEValue:

Measure for CustRebate - Central - DETarget:


Measure for FA GM - Total - DEValue (can't seem to upload this one so I have pasted the DAX code):
------------------------------------------------------------------------------------------------------------------------------------------------------------

FA GMDEValueTotal =

VAR GrossProfit =
CALCULATE( SUM( tbl_Daily_E2E_FC[DEValue] ),

          CONTAINSROW(

                      {"North", "South West", "South East", "Central"}, tbl_Daily_E2E_FC[Division] ),

          FILTER( tbl_Daily_E2E_FC, tbl_Daily_E2E_FC[Grouping_Level] = "Gross Profit" ) )

VAR Sales =
CALCULATE( SUM( tbl_Daily_E2E_FC[DEValue] ),

          CONTAINSROW(

                      {"North", "South West", "South East", "Central"}, tbl_Daily_E2E_FC[Division] ),

          FILTER( tbl_Daily_E2E_FC, tbl_Daily_E2E_FC[Grouping_Level] = "Sales" ) )

RETURN

    DIVIDE( GrossProfit, Sales, 0 )

-----------------------------------------------------------------------------------------------------------------------------------------------------------

So hopefully from the three example measures shown you can see the DAX involved to replicate the original Excel formulas.

Is there a better way to achieve what I am trying to do?   How would you guys/gals go about this?

Is there a way to use DAX to create, say, a CALCULATEDTABLE on the fly by using the ADDCOLUMNS function, taking into consideration the Division and Groupling Level rows/columns and where there is an intersect point then I must paste the DAX measure for that partcular result.

It is very difficult for me to explain clearly what I am wanting to do via this post so I am hoping from Starting and End screenshots, along with the measures that populate the non-aligned rows will show you what I am trying to do.

If any of you experts can help then it will be much appreacited.

Thanks in advance.

[MFelix]

Hi @Anonymous,

Currently Ivm not on my computer so I cannot give you a complete example.

But just giving you some highlits, DAX measures work based on context and this can be given on the filters, visuals, etc. considering this the way you use your columns will influence your result.

Picking one of your measures the FILTER part where you refer the division is not needed since if you add the division on your matrix the measure will filter out the division.

To better help and give you a complete explanation can you share your sample data in table format or a file so it's easy to copy to a PBI file?

Don't now the full extend of your model and setup but believe that you will not need to create 80 measures for making your calculations.

Regards,
MFelix

Regards,
MFelix

[Anonymous]

@MFelix  - thanks for responding. I could really do with some help on this one as the way I'm doing it is a very manual way of doing it and I'll be suprised if there isn't a better way, although this is the pain to be had when trying to replicate Excel in Power BI.

Firstly, the dataset snapshot I posted in the first post I do not have to hand as it is on a work computer, however I have created another dummy dataset in Excel here (OneDrive): https://1drv.ms/x/s!Anaw2mAENZAIZzh2MP2sleZaQWk

The datset is brief but it is enough to work with to get what I hope can be done working.

I understand that DAX measures work on context. For example, to populate the Matrix visual I am using two measures DEValue and DETarget. The aggregation used in the visual is SUM. At the intersect point, say, for North and Sales the DEValue and DETarget values are filtered automatically by North and Sales using the Matrix's intersect point/filtering.

But... the rows of SSM, FCP, Cust Rebate, Sup Rebate, and FA GM are not single values aggregated (i.e. summed). The values in these rows are working out a calculation, to give an example:
The DEValue by North for SSM is:

So you can see it is calculating two individual measures and then doing a final calculation based on those. Now you might say well this cane done all the calculations used in the measure are under the North division still, but what about another measure I posted where it has two go across multiple divisions like:

FA GMDEValueTotal =

VAR GrossProfit =
CALCULATE( SUM( tbl_Daily_E2E_FC[DEValue] ),

                  CONTAINSROW( {"North", "South West", "South East", "Central"}, tbl_Daily_E2E_FC[Division] ),

                  FILTER( tbl_Daily_E2E_FC, tbl_Daily_E2E_FC[Grouping_Level] = "Gross Profit" ) )

VAR Sales =
CALCULATE( SUM( tbl_Daily_E2E_FC[DEValue] ),

                   CONTAINSROW( {"North", "South West", "South East", "Central"}, tbl_Daily_E2E_FC[Division] ),

                   FILTER( tbl_Daily_E2E_FC, tbl_Daily_E2E_FC[Grouping_Level] = "Sales" ) )

RETURN

    DIVIDE( GrossProfit, Sales, 0 )

What I can say that the DAX expression for each row's column is the same, meaning that SSM for north is filtered against the north division, SSM for South West is filtered against the South West division, etc.. It is only the Total measure (the measure above) that has to combine multiple regions.

Once I've worked out the SSM row then I need to calculation the values for the FCP row which will follow the same relations are SSM but just on different Grouping Levels. See the CustRebate measure in the first post to an example of how the CustRebate row will need calculating.

If I could get all this in a single table like the matrix in the visual then all will be so easy to allign.

How do I achieve this?  Surely, many will have been asked to replicate an Excel report in Power BI (rightly or wrongly) so how do you could about this?

On a second part, Excel allows for individual cell based conditional formatting. If I can get the above in a single table then how would I go about conditional formatting certain cells? It isn't for all rows. The conditional formatting is only required on the Sales and FA GM rows.

@MFelix (and anyone else) I appreacite this may seem like a difficult task but I do imagine it is a comon request so if you can help me out and guide me on this I will be very appreciative.

Thanks.

[MFelix]

Hi @Anonymous ,

You are correct about the approach organizatiosn are having regarding Power BI and they want to have similar reports to Excel (this happens to me on a daily basis at my work place), however it's important to realize that PBI as so different possibilities and can be used to show data in different ways, so companies need to let go of the old reporting and take advantage of the interactivity of PBI. But this is a mind set that is difficult to change.

Regarding your issue, I have looked at your data and, working in PBI (or any other BI tool) we need to understand that the most important step (in my modest opinion is the way we setup the data base) becuase this impacts how we are making our life easier on the calculations.

In your model I have made a unpivot of the MTDValue, MTD Target, DE Value and DE Targe

So I go t a table with 4 columns:

Division

Grouping Level

Category

Value

Based on this I can do the following measure that need less variable in the calculations:

FA GMDEValueTotal = 
VAR GrossProfit =
    CALCULATE (
        SUM ( Sales[Value] );
        Sales[Grouping_Level] = "Gross Profit";
        ALL ( Sales[Division ] )
    )
VAR Sales_Total =
    CALCULATE (
        SUM ( Sales[Value] );
        Sales[Grouping_Level] = "Sales";
        ALL ( Sales[Division ] )
    )
RETURN
    DIVIDE ( GrossProfit; Sales_Total; 0 )



CustRebate = 
VAR sales_total =
    CALCULATE ( SUM ( Sales[Value] ); Sales[Grouping_Level] = "Sales" )
VAR custRebate =
    CALCULATE ( SUM ( Sales[Value] ); Sales[Grouping_Level] = "Customer Rebate" )
RETURN
    DIVIDE ( ( custRebate * -1 ); sales_total; 0 )


SSM = 
VAR sales_total =
    CALCULATE ( SUM ( Sales[Value] ); Sales[Grouping_Level] = "Sales" )
VAR costs =
    CALCULATE ( SUM ( Sales[Value] ); Sales[Grouping_Level] = "Costs" )
RETURN
    DIVIDE ( ( sales_total - costs ); sales_total; 0 )

As you can see I'm only makingt filter on 2 columns because all the rest is based on the context of the values, This can even be made in different ways if you have dimension tables for the Division, Grouping Levels and Category.

The way you setup things really impacts in the way you will make the calculations.

To further help you can you please check if based on your mockup data the PBIX file is calculating the 3  measures above correctly?

As you can also see in the file I have made two pivot tables, again this as to do with the way we present things in PBI.

Please give me your comments and corrections on this setup, so I can assist you further as I already mentioned.

Regards,

MFelix

[Anonymous]

@MFelix  - thanks for this. It's really helpful.

If you have, on a daily basis, colleagues asking for Power BI reports to mimic Excel reports then how do you respond?  Do you go with it as it's their requirement or say no Power BI is intended to shows data via other ways?

Ragarding the best approach to modelling data, do you have a favourite link or resource on the subject?

As this dataset is so small would you bother creating dimension tables for Division, Grouping_Level, and Category?
I have read that as the vertipaq engine is in-memory it should be able to handle data, even a larger volume of data, in a single flat table, and creating dimension tables with the in-memory database is really only for segregating the dimension attributes for easy drag n' drop useage, or if you have multiple fact tables that need to work off the same dimension table. Would you agree?

The FA GMDEValueTotal table isn't correct or, I should say, how I need it. The measure filtered by Dimension and Grouping_Level should be:

However at the end of the visual where we have 'Total' it should be the total value for all Dvisions but only for DEValue, then immediately next to it should be another Total column where it adds up all Divisons but this time for DTTarget. By having Category column in the Matrix's Columns section it doesn't allow for this Total for DEValue and Total for DETarget. Is there a way around this or should I look to create a new Matrix with to different measures to calculation the totals only and hard-code the Category, along with the Division and Grouping_Level filters)?  If you look at Matrix screenshot in my first post you will see what I mean or take a look at the below knocik-up:

With the two Matrixs in use, one showing CoS Adj, Sales, SSGP, etc.. and the other showing SSM, CustRebate etc.. If I needed to conditional format those cells individually then can this be done, or is it a limitation of using Power BI for this kind of conditional formatting requirement?

Thanks again @MFelix 

[MFelix]

Hi @Anonymous ,

I didn't forgot about your question as I refereed I have some issues at work. Will try to get back to you soon.

Sorry for the delay.

Regards,

MFelix

[Anonymous]

This topic is also of extreme interest to us. Thank you for the info so far and we look forward to it progressing. 

[MFelix]

Hi @Anonymous ,

First of all let me apoligize, since this week I'm preety busy at work and will have a hard time answering to your full post with detail you need.

Regarding the first question, the change from Excel to PBI needs a change in mind set, when I get requests I try to  work together with people to show them the different options there are, and some times it's works some times they continue to insist on the same type of visualization, on this I cannot give a best approach since depends on your target audience, their open mind to new ways of doing and on our capacity to prove concepts. It's difficult and time consuming.

Conserning the question about the flat table you are correct, however if you look at a SSAS model, you have flat tables for the data but the end user see several tables with dimension, this is because it helps to make the context of the calculations because if you have all your data in a single table, what will happen is the problem you have every single change in parameter as to be push to the measures, also depending on the way the flat table is created. 

As I said, I will get back to you during the week with the rest of explanations.

Sorry for not being abble to answer you completly but want to prepare a more complete answer.

Regards,

MFelix

[Anonymous]

In playing around further on this, I went on to create a measure that didn't have the Division filter hard-coded, instead I will let the Matrix visual's automatic filtering (intersect point) filter the division. The measure if this:


The thing is I have to explicitly hard-code the Grouping_Level filters as the measure's calculation refers to different rows (i.e. Sales AND Costs). Anyway the end result looks like the below:

The values are correct but I don't the rows to shows Costs, Sales, and Total names, I just need the total row's values with the total name being 'SSM'.
I would then have to repeat this process for each of the rows I need to fit in a single table - see the screenshot in my first post.

I could use the Row and Union functions to call each measure. Of course I will need need to keep the 80 or so measures I've created but by using the Row and Union method I should be able to have all aliigned nicely.

The problem with this method is if the values/measures/cells require it's own independent consitional formatting, like you can do in Excel, this can't happen in this method and maybe I will have to keep to my manual intensive way of placing a single measure in it's own individual table and allign all.

Again, please help guys/gals?  I would like to know what is the best practice for this type of requirement??

Thanks.

[MFelix]

Hi @Anonymous ,

Regarding the Sales, Cost, and So on you need to have them explicit on the calculation of the main measure like SSM and so on but this is because you have to give what is the calculation going to behave since it's percentages, like in excel you need to point to the current column or columns.

But as I posted on my previous item check the way I make one SSM that allows to make all the values for each group and district.

Regarding the conditional formatting you can also do it in a broadly way.

Again don't believe that you need to have 80 measures to have your final result.

Get back after checking the PBIX file I have posted previously.

Regards,

MFelix