Creating derived column values based on subtotals from another column

[Anonymous]

Hello everyone,

I recently came across an interesting question here.

Below is the screenshot of my pivoted data:

In the above data, the 'mratio' measure is the ratio of [Bug Efforts] and [Bugs and Efforts] columns in the left of the ratio.

I need another column named 'Goal' for each year which is  (1.25 * subtotals of mRatio for each year).

So, all rows for all months for year 2019 in this 'Goal' column will have a value of 1.25 * 5.16. Here, mratio: 5.16 is the subtotal value for end of year 2018.

Likewise, all months of 2018 will have a similar calcuation coming from year 2017.

[v-yulgu-msft]

Hi @Anonymous ,

You can add below measure into Matrix visual.

previous year mRatio =
1.25
    * CALCULATE (
        SUM ( Sample2[Bugs Efforts] ),
        FILTER (
            ALLSELECTED ( Sample2 ),
            Sample2[Year]
                = SELECTEDVALUE ( Sample2[Year] ) - 1
        )
    )
    / CALCULATE (
        SUM ( Sample2[Bugs and Defects] ),
        FILTER (
            ALLSELECTED ( Sample2 ),
            Sample2[Year]
                = SELECTEDVALUE ( Sample2[Year] ) - 1
        )
    )

Best regards,

Yuliana Gu

[Anonymous]

@v-yulgu-msft 

Thanks Yuliana. 

I have a separate Date table named 'dCalender' that handles the Year and Months instead of coming from the Sample2 data set.

So, I modified your DAX query to the below and gives me an error over the [CalenderYear]:

previous year mRatio =
1.25
* CALCULATE (
[Bugs Efforts],
FILTER (
ALLSELECTED ( QAData ),
dCalender[CalendarYear]
= SELECTEDVALUE ( dCalender[CalendarYear]) - 1
)
)
/ CALCULATE (
[Bugs Efforts] ,
FILTER (
ALLSELECTED ( QAData ),
dCalender[CalendarYear]
= SELECTEDVALUE ( dCalender[CalendarYear] ) - 1
)
)

Error returned: 'A single value for column 'CalenderYear; in table dCalender' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying ab aggregation such as min, max,count or sume to get a single result'

Also, the measure 'Bug Efforts' used in the above DAX query has been calculated as:

Bugs Efforts = CALCULATE(

                                          [Total Efforts],

                                               FILTER (QAData, 

                                              QAData[CreatedDate] >= MIN(dCalender[DimDateKey]) && QAData[CreatedDate] <=                                                MAX(dCalender[DimDateKey])

                                                         )

                                           )  

Could you please help in updating the query to fix this issue.

[Cmcmahan]

So because it's a separate table, you need to specify that you're using Sample2's year.  This way, DAX knows which year to use.  I assume you have a relationship between a a date column in Sample2 and dCalendar?  

dCalender[CalendarYear]
= YEAR(SELECTEDVALUE(Sample2[Date])) - 1

If you could take a screenshot of the relationship between dCalendar and Sample2, that would be very useful in figuring out exactly what parts you need to specify

[Anonymous]

I do not have any relationship between the 'dcalender' table and the 'Sample2' table. They are no 1-* or 1-1 relationship between them.

Below are the tables: 

For this reason, I was using the logic of :

Bugs Efforts = CALCULATE([Total Efforts],FILTER (QAData,
QAData[CreatedDate] >= MIN(dCalender[DimDateKey]) && QAData[CreatedDate] <= MAX(dCalender[DimDateKey]))) +0

I did this because there are two other dates 'StartDate' and 'ClosedDate' in Sample2 dataset.

I suppose making 1-* relationship will defeat the purpose of using the date column from dCalender table with three dates in the Sample2 dataset.

[Cmcmahan · Accepted solution]

It's still useful to have a date dimension.   It just has to be related to the data.  Otherwise it's like I hand you a QA report with different dates on it, and a stack of calendars, and asked you to pick the calendar from the previous year. Without being told that I wanted the previous year to the CreationDate on that report, you're not sure which calendar to pick.  

Here's an article about some of the weirdness with date dimensions in PowerBI: https://radacad.com/power-bi-date-dimension-default-or-custom-is-it-confusing  If you wanted to filter by the day of the week, evaluate functions ignoring weekends, quickly sort by the day of the week, or apply other information (like whether a day is a holiday) to specific days in a calendar, a date dimension is EXTREMELY useful. 

In your example, it might be simpler to just calculate your measure from Sample2 like this:

previous year mRatio =  //only works in the context of having 1 QAData row selected
1.25
* CALCULATE (
[Bugs Efforts],
FILTER (
ALLSELECTED ( QAData ),
QAData[Created Date].[Year] = SELECTEDVALUE ( QAData[Created Date].[Year]) - 1)
)
/ CALCULATE (
[Bugs Efforts] ,
FILTER (
ALLSELECTED ( QAData ),
QAData[Created Date].[Year] = SELECTEDVALUE ( QAData[Created Date].[Year]) - 1 //this doesn't seem right. your numerator and denominator will always be the same!
)
)

The issue before is that when you were using SELECTEDVALUE to pick a date to find the previous year from, DAX had no way to figure out which date was selected, unless your context already had you filtered down to a specific year. Even now, when changing it to use the CreatedDate, I ran into an issue where I wasn't sure which QAData date to use in both the numerator and denominator.  This is using the same selection in both, so you'll always get a value of 1.25 from [previous year mRatio]. 

As far as your Bugs Efforts measure, I'm not sure it's as effective as you want.  Right now, it returns all rows where the CreatedDate is within the current calendar selection.  If you set up relationships between all your date columns and your date dimension, you would get the same results with this measure:

Bugs Efforts2 = CALCULATE([Total Efforts], USERELATIONSHIP(QAData[CreatedDate], dCalendar[DimDateKey]))

I'm assuming [Total Efforts] is a measure of some sort?  Either way, you can use USERELATIONSHIP to specify which date you're using for each measure, while still having them all use other data from your calendar.

If you could explain which values you want to use, as well as the context you want to use to determine what your current value is, I'm happy to assist further.

[Anonymous]

@Cmcmahan 

Thank you for sharing all these valueable points and for quick responses.

I will try to implement these recommendations in next couple of days and see it this worked or not.

[v-yulgu-msft]

Hi @Anonymous ,

Have you worked it out? If so, please kindly accept the helpful reply as an answer or sharing your resolution so that it can benefit more users.

Best regards,

Yuliana Gu