Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

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.
Need Help

9 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago
    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        7 years ago

        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