Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
ThomasSan
Helper IV
Helper IV

Have calculate distinguish column values

Hi everyone

 

In one of my dashboards, I am attempting to create a graph that looks as follows:

ThomasSan_1-1671645387208.png

The idea is to compare the performance over the course of a year of multiple years.

 

To do that, I have set up the following visual:

ThomasSan_0-1671645332558.png

My current measure looks as follows:

Test Measure = 
var curr =
CALCULATE(
    sum('Table'[SalesTotalEUR]),
    KEEPFILTERS(
        'Table'[Date]
    )
)

var refer =
CALCULATE(
    sum('Table'[SalesTotalEUR]),
    FILTER(
        ALL('Date'[Date]),
        Month('Date'[Date])=1
    )
)

var div=
DIVIDE(
    curr,
    refer,
    ""
)

return div 

 

My problem is that I am unable to keep my measure restricted to the year column. Therefore, all my January values (be they from 2021 or from 2022) are being used in my calculations. Can anyone therefore please tell me how to set up my measure so that it distinguished between the column values?

 

 

1 ACCEPTED SOLUTION
ThomasSan
Helper IV
Helper IV

So, after some more researching and plenty of trials and errors, I was finally able to find the correct dax command for the problem It is as follows:

 

 

 

Test Measure = 

var yearselected=
SELECTEDVALUE('Date'[Date].[Year])

var curr =
sumx('Table', 'Table'[SalesTotalEUR])

var refer =
CALCULATE(
    sum('Table'[SalesTotalEUR]),
    FILTER(
        ALL('Date'[Date]),
        Month('Date'[Date])=1 && year('Date'[Date])=yearselected
    )
)

var div=
DIVIDE(
    curr,
    refer,
    ""
)

return div

 

 

 

As you can see, as SELECTEDVALUES that has to be brought in as a variable is needed.

 

The resulting visual looks as desired:

ThomasSan_0-1671709140730.png

 

View solution in original post

6 REPLIES 6
ThomasSan
Helper IV
Helper IV

So, after some more researching and plenty of trials and errors, I was finally able to find the correct dax command for the problem It is as follows:

 

 

 

Test Measure = 

var yearselected=
SELECTEDVALUE('Date'[Date].[Year])

var curr =
sumx('Table', 'Table'[SalesTotalEUR])

var refer =
CALCULATE(
    sum('Table'[SalesTotalEUR]),
    FILTER(
        ALL('Date'[Date]),
        Month('Date'[Date])=1 && year('Date'[Date])=yearselected
    )
)

var div=
DIVIDE(
    curr,
    refer,
    ""
)

return div

 

 

 

As you can see, as SELECTEDVALUES that has to be brought in as a variable is needed.

 

The resulting visual looks as desired:

ThomasSan_0-1671709140730.png

 

AlexisOlson
Super User
Super User

You shouldn't need a new measure to generate a chart like this. Put Month on the x-axis and Year in the Legend box like this:

 

AlexisOlson_0-1671647863359.png

Hi @AlexisOlson ,

 

thanks for your reply. How would you set up your example to have your Sales Amount values normalized to 100 in January of every year? My goal is to show the relative change of my sales figures over the course of a year (e.g. sales grew by 10% in February 2021 (i.e. line goes to 110) while sales in February 2022 decreased by 6% (i.e. line goes to 94)). You can see what I mean in the graph above.

 

How would you approach this?

Ah, I missed that detail. Try this:

Normalized Sales = 
DIVIDE (
    [Sales Amount],
    CALCULATE (
        [Sales Amount],
        REMOVEFILTERS ( 'Date'[Month Number] ),
        'Date'[Month] = "January"
    )
)

The removes the month name and month number filter context without removing the year filter context.

Hi @AlexisOlson ,

 

unfortunately, this only fills out the January cells in my table

ThomasSan_0-1671695158534.png

I have slightly modified your command in order to fit my tables

Test 3 = 
DIVIDE (
    sum('Table'[SalesTotalEUR]),
    CALCULATE (
        sum('Table'[SalesTotalEUR]),
        REMOVEFILTERS ( 'Date'[Month Number] ),
        'Date'[Date].[Month] = "January"
    ),
    ""
)

 

Any idea how to have the CALCULATE section of your solution expanded to all cells?

I'm guessing your model doesn't have exactly the same columns with the same names as the file I was testing with. Do you have columns for month name, month number, and year in your date table? If so, what are they? If not, then my solution clearly won't work since it's referencing non-existing things.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.