Forum Discussion

bortxs's avatar
bortxs
Frequent Visitor
9 years ago

Table with two value-columns and their delta

Hello guys, 

 

I´m trying to create a table showing information of one week and previous week (it can also be month, this is not relevant) together with its delta. The idea is to create something like this:

 

 Calendar Week 30Calendar Week 31Δ%
Net Order Volume15.00016.50010%
Shipping 1.000800-20%
Total Net Order Volume16.00017.3008%

 

Unfortunately I only get to have the weeks on the left side as rows (where the KPIs are), but not as columns, so that under each week I can show not only Net Order Volume but also number of orders, number of items ordered, or some other KPIs. Any idea?

 

Thanks a lot for your help!

 

Best regards,

 

Borja

Need Help

4 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    bortxs,

     

    You may refer to the following DAX that creates a new table.

    Table =
ADDCOLUMNS (
    GROUPBY (
        Table1,
        Table1[KPI],
        "Calendar Week 30", SUMX ( CURRENTGROUP (), IF ( Table1[Calendar Week] = 30, Table1[Volume], 0 ) ),
        "Calendar Week 31", SUMX ( CURRENTGROUP (), IF ( Table1[Calendar Week] = 31, Table1[Volume], 0 ) )
    ),
    "Δ%", DIVIDE ( [Calendar Week 31] - [Calendar Week 30], [Calendar Week 30] )
)
    • bortxs's avatar
      bortxs
      Frequent Visitor
      9 years ago

      Thanks v-chuncz-msft! 

       

      I think I didn´t express myself correctly... I said I wanted a table (indeed, that´s what I want...) but I suppose the correct name for that would be a "Table-Visual"... If I understood correctly, your solution would create me a new table, right? 

       

      I have created this (see below) for a similar purpose using  3 card-visuals and a rectangle... the idea would be to create something similar but not so "rudimentary" (if possible), so that I can have few different KPIs under each "column" (be it YTD vs PYTD, be it CW31 vs CW30...)

      Sorry for the confusion! and thanks a lot again!

       

      Borja

       

      • bortxs's avatar
        bortxs
        Frequent Visitor
        9 years ago

        Hello guys, 

         

        regarding this topic I thought I could do something like this...

         

        I´ve created a Slicer with the Calendar Weeks (CW) and besides that I´ve created a column in the Dates-Table that gives me automatically the week before (very simple: when I select the CW25, it gives me the CW24 etc.). 

         

        CW Slicer

        My first solution it´s been to create two slicers and "unlock" each "Multirow Card" from the week I don´t want it to be influenced by... but I find it a bit too messy. 

         

        Small table above with the CW and PCW fields, below the needed KPIs in 2 "Multirow cards"

         

        In order to optimize it a bit, I would like to create a measure which reacts not to the week selected on the slicer (I want to have only one) but to the value corresponding to "the week selected on the Slicer -1 (minus one)". 

         

        I have created this formula: "Net Order Value PW = CALCULATE(SUM('DATAVALUES'[Net Order Value]);CW[PCW])" but unfortunately it´s not working properly... :smileyfrustrated:

         

        Would you guys have an idea of how the DAX function should look like in this case? Thanks!!

         

        Borja