Reply
Frequent Visitor
Posts: 8
Registered: ‎06-26-2017

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

Community Support Team
Posts: 4,051
Registered: ‎07-09-2016

Re: Table with two value-columns and their delta

@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] )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 8
Registered: ‎06-26-2017

Re: Table with two value-columns and their delta

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...)

Visits Table.PNG

Sorry for the confusion! and thanks a lot again!

 

Borja

 

Frequent Visitor
Posts: 8
Registered: ‎06-26-2017

Re: Table with two value-columns and their delta

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.PNGCW 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. 

 

CW and KPIs.PNGSmall 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... Smiley Frustrated

 

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

 

Borja

Frequent Visitor
Posts: 8
Registered: ‎06-26-2017

Re: Table with two value-columns and their delta

I´ve tried with the solution from @v-ljerr-msft from this thread: (https://community.powerbi.com/t5/Desktop/Cards-to-show-previous-period-and-or-exclude-current-period...). I adapted the code and got this:

  

Net Order Value PW = 
VAR currentWeek = MAX(DATUM[Week]) 
VAR currentYear = MAX(DATUM[Year]) 
RETURN (IF(currentWeek=1;CALCULATE([Net Order Value];FILTER(ALL(DATUM);DATUM[Year]=currentYear-1 && DATUM[Week] = 52)); CALCULATE([Net Order Value];FILTER(ALL(DATUM);DATUM[Year]=currentYear
&& DATUM[Week] = currentWeek-1))))

 

...but unfortunately it´s not working either... 

 

Thanks for your comments/help! Smiley Happy