cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bortxs
Frequent Visitor

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

4 REPLIES 4
Community Support
Community Support

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.
bortxs
Frequent Visitor

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

 

bortxs
Frequent Visitor

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

Highlighted
bortxs
Frequent Visitor

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! 🙂

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors