Problem of the Week #7 (DAX) - Dynamic Report with Calculation Groups

[MudassirAli]

OK, here we go! Problem of the Week #7 is now live!

For background on this initiative, check out this post .

Note: detailed intro video to this problem has been posted on YouTube, and can be found here

Background

As Tabular Editor 3 is about to be released therefore, it is a perfect time to get some hands-on practice in Calculation Groups. With this challenge, you will learn how to:

• Create Time Intelligence Calculation

• Create Other Metrics

• Exclude Specific Measures

• Apply Dynamic Formatting

• Use Measures as Small Multiples

• Sort the Calculation Items

The Model

The Contoso Model is being used for this challenge having a Date Table, Fact Table & One Dimension Table.

Your Task

This week’s problem consists of the following requirements:

1. Time Intelligence Calculation with Calculation groups. The calculation items should change the measures dynamically in the report. To learn how to create time intelligence calculation items, refer to the EDNA Video.
   

2. Create Other Metrics. While creating other metrics, it is to be noted that when the Top 5 & Daily Average Calculation Items are selected, the Table Visual Should Not show the Margin %. To learn more about this trick, refer to the functions: ISSELECTEDMEASURE() & SELECTEDMEASURENAME().
   The requirement for MAX & MIN is to get the highest Value or Lowest Value on a particular day in a particular month. For example, if in Jan the highest value is on 14-Jan-2011 then the value of that day is required.
   Similarly, the requirement for Top 5 is to get the Top 5 values on a Daily granularity in particular months.
   

   
   
   
   
   
   
   
   
   
   
   
   
   

3. Apply the correct format string to Calculation Items.
   

   
   
   
   

4. Use Measures as Small Multiples. Refer to the EDNA Video to use measures as categories in the report with the help of Tabular editor.
   

   

5. The last requirement is to sort the Calculation Items in Small Multiples Visual in the following order: Sales, Cost & Margin. Refer to the EDNA Video to sort the Calculation Items in Tabular editor.

All in All, 3 Calculation Groups are required for this challenge:

• Time Intelligence

• Other Metrics

• Measures as Calculation Groups

Important
We ask that if you post anything directly related to a solution to please use the “Hide Details” or “Blur Spoiler” options within your post. This will prevent people who have not yet completed their solution from seeing things they might not want to see yet and could influence their solution.

To use these options, just select the text or image you want to hide, click on the gear icon on the top right at the top of your post and select either Hide Details or Blur Spoiler.

Note that this is a DAX-only challenge, so no Power Query even if that is what you would choose to use outside of this challenge.

Eligibility for the Drawing
To encourage participation, we will be doing a drawing of five random participants each round to receive one of these cool Enterprise DNA vinyl laptop stickers:

To be eligible for the drawing, just send an email to problemoftheweek@enterprisedna.co before 11:59pm ET, Wednesday, March 10, 2021 indicating you’ve successfully completed the challenge, and please provide your mailing address for us to send the sticker if you’re selected.

We hope you find this initiative fun, challenging and helpful in continuing to build your Power BI skills. All of the information you will need is contained in the files within this post. If you have any questions or comments, just message @BrianJ or @MudassirAli in this forum thread.

Good luck, and enjoy!!!

POTW # 7 - Dynamic Report with Calculation Groups.pbix (49.3 MB)

P.S. Your feedback is always welcome, and if you have a real-world problem that you think might make a good future Problem of the Week, please email it to us at problemoftheweek@enterprisedna.co.
Thanks!

[tweinzapfel]

@MudassirAli - definitely a great topic. I was unfamiliar with calculation groups so this is an excellent topic.

I had a couple of minor questions.

1. I’m assuming we should mirror the calculation groups that you have listed (i.e. PM, PY, MOM, DAD, for “Time Intelligence” and then Daily Average, Max, Min, etc. for “Other Metrics”). Is this a correct assumption?

2. If yes to #1, I was just curious on the Min and Max given the picture examples shown. If these are to be the Min/Max sales for the month - then the pictures aren’t showing that (i.e. in Jan 2011, there are individual sales of < $100.). So I wasn’t sure if there was some additional clarity on what area the min/max was supposed to be aligned to (or is that really up to us)?

Thanks,

Tim

[MudassirAli]

@tweinzapfel

1. Yes, the time intelligence calculations should be same as mentioned n the post.

2. My apologies for not explaining this part. The requirement for MAX & MIN is to get the highest Value or Lowest Value on a particular day in a particular month. For example, if in Jan the highest value is on 14-Jan-2011 then the value of that day is required.

Let us know if you have more questions.

Thanks.
Mudassir

[Craig]

My Entry for Problem #7

POTW # 7 - Dynamic Report with Calculation Groups Craig…pbix (49.3 MB)

[MudassirAli]

@Craig
Thank you for participating in the challenge & Great work on this one. There are few points to note here:

The formatting to be same throughout

The Percentage format to be correct in Small Multiple Visual

Top 5 values not correct in the Table Visual

Apart from the items mentioned, you have provided an excellent solution.
Let us know if you have more questions.

Thanks.
Mudassir

[Jihwan]

Hi, @MudassirAli
This is the first time I upload my pbix file directly to the forum. Until today, I always submitted my pbix file by email and I was helped to upload it.
Please correct me if I uploaded it in the wrong place.
Please check my solution file, and I look forward to hearing your great feedback.

The below is what I have struggled.

1. I tried to only show information from Jan 2011 to Dec 2013.
   When showing PM, then from Feb 2011 to Dec 2013.
   When showing PY, then from Jan 2012 to Dec 2013.
   I was thinking that, if it shows PM or PY but if it is other than the range that is mentioned above, it would not be so meaningful.
   I am not sure whether or not I wrote DAX correctly in Tabular Editor, but I think I wrote quite a long one. I think there should be a cleaner way to write.

2. In Tabular Editor, I tried to find a way to show the number format with red color if it is minus. For instance, “#,##0;[RED](#,##0)” (like excel??). However, I failed. So, I just wrote “$#,##0” for sales, cost, and margin. Margin% shows percentage format.
   If it is OK with you, could you please advise how to write it if I want to show red color with () when the number is minus, for instance, -3,254 = (3,254)-> I mean, (XXX) in red color.

3. I changed the precedence of calculation groups like below. Otherwise, my calculation would not work.

• Time Intelligence = 1
• Other Metric = 0
• Small multiples = 2

Thank you very much.POTW # 7 - Dynamic Report with Calculation Groups (by Jihwan Kim).pbix (49.3 MB)

[Craig]

Hi Mudassir,

Thanks for the constructive feedback. I’ve managed to fix the oversights you’ve highlighted. However, I am still a little baffled as to how my Top 5 Measure was able to calculate correctly in the small multiples visual but fail to deliver the same results in the table visual.
I initially went for a vanilla CALCULATE() over TOPN() combination. I’ve changed it now to SUMX() over a virtual table using ADDCOLUMNS() over VALUES(‘DATE’[DATE]). Nothing spectacular, but it does seem to work. I’m not sure why this should be the case though, as the Filter context in both visuals should be the same. Naturally, with this in mind I didn’t even check the results as I was convinced they would be identical. Boy, was I wrong! So, any further feedback would be greatly appreciated in that area. Further to that, an in depth look at the Format String area of Tabular Editor would be greatly appreciated when it comes to the You Tube synopsis. It would be great to have a summary of what the available format options are. Thanks again. Craig. This text will be blurred

[MudassirAli]

@Jihwan Thanks for participating in the challenge. Very well done on the solution and a really interesting question about Dynamically coloring the negative values. I have been looking into this and I am sure will crack this soon. Yes, you wrote long DAX formulas but they are interesting to look at. If the solution is right and the visuals loading fast, it doesn’t matter whether the code is short or long.

You are spot-on on the Precedence in calculation groups. Without this, you cannot get correct results.

Thanks again for participating.

Mudassir

[MudassirAli]

@Craig Honestly speaking, I was also surprised by the Top 5 results and I didn’t check it at first. However, when I was looking into it in detail, it really took me by surprise. I think the only reason of this would be is that the calculation items override the filter context in the report and in small multiple visual, we are using the calculation item therefore, the results are correct but in the table visual, we are using the measures & the combination of calculation of groups giving us the erroneous results.

For the custom format strings in Power BI, you can refer to this website for in-depth look in custom format strings.

Thanks.

[jbressan]

Hi @MudassirAli

Are you sure that your total Daily Average are correct ?

[MudassirAli]

@jbressan Yes, they are correct.

[Yacob]

POTW # 7 - Dynamic Report with Calculation GroupsYB.pbix (49.3 MB)

Summary

This text will be hidden

Please find my Solution.

[jbressan]

Hi @MudassirAli

This is my solution for this POTW #7

POTW # 7 - Dynamic Report with Calculation Groups - JoseBressan.pbix (49.4 MB)

Online Version

[BrianJ]

@jbressan,

Well done. And I love the look of your solution. You may see me borrow that in a future Data Challenge entry…

• Brian

[tweinzapfel]

Everyone,

Attached is my solution. I definitely enjoyed this one and learning a new technique.

POTW #7 - Final.pbix (49.3 MB)

[logicalbi]

Please find attached my version. This will certainly cut down the number of measures needed.

POTW # 7 - Dynamic Report with Calculation Groups.pbix (48.7 MB)

[MudassirAli]

Hi All,

Here I am again wrapping up the Problem of The Week # 7 which is I think was difficult given most of the people haven’t dived into Tabular Editor & Calculation Groups. The main reason of throwing Calculation Groups challenge was to give some hands-on practice to both our members & non-members before the launch of Tabular Editor 3 which I have heard is epic.

Main Complexities

The tricky part was to get the

1. Correct Format Strings

To dynamically apply the correct format string it is necessary to set the formatting for each calculation items either through a DAX expression or without it. For example, if I want to select Margin %, I don’t need any other formatting except the % format therefore, expression is not required. However, when selecting other calculation items like PM, PY, MOM & YOY in Time Intelligence calculation group we have to apply the DAX expression so the % format is shown when these items are selected for Margin % and $ format for other measures like Sales, Cost & Margin.

   Dynamic Formatting = 
    IF(
    SELECTEDMEASURENAME() = "Margin %",
    "#,0.00%",
    "$#,0")

2. Correct Precedence

When using multiple calculation groups in a report, it is very easy to lose sight of the correct results if the precedence is not set correctly. The highest precedence should be give to the Calculation Group, the application of which should be applied first in the report. In this case, the highest number to be given to the Calculation Group called “Measures Select” which is just referring to the main measures in the report namely Sales, Cost, Margin & Margin % because these are the core calculations in the report.

The second precedence to be given to Time Intelligence calculation as we want our results to be evaluated under the correct Time Frame/Context. For e.g. if we look at “Daily Average” in “Other Metrics”, we want the Daily Average to be applied under the time frame mentioned in Time Intelligence. For e.g. first we want the “PM” application and then want the Daily Average to be evaluated under the context of previous month. If we give the higher precedence to “Daily Average” than Time Intelligence, it will calculate the average without being bound to the time frame selected in Time Intelligence calculation group and will give erroneous results. That’ why, the “Other Metrics” will get the lowest precedence.

3. Top 5

This part was the most interesting and surprising as with simple TopN Dax expression, the results were incorrect in the Table Visual but correct in Small Multiple Visual. The reason is we are using Calculation Groups in small multiple visual but not in table visual. Calculation groups override the filter context in the report but actual measures cannot.

Top5 Wrong result =
 CALCULATE( 
            [Cost] ,
            TOPN( 
                  5 ,
                  VALUES( 'DATE'[DATE] ) ,
                  [Cost]
                  )
)

Therefore, we had to make changes in the code to make the TOPN work by forcing the filter context by grouping Date column & measure(s).

Top5 Correct Result =
    VAR vTable =
        ADDCOLUMNS (
            VALUES ( 'Date'[Date] ),
            "@Val", SELECTEDMEASURE ()
        )
    VAR Top5 =
        TOPN (
            5,
            vTable,
            [@Val], DESC
        )
    VAR Result =
        IF (
            SELECTEDMEASURENAME () = "Margin %",
            BLANK (),
            SUMX (
                Top5,
                [@Val]
            )
        )
    RETURN
        Result

The rest of the tasks were pretty straight-forward that was to use measures as small multiples and to get the correct sort order in Tabular editor.

Conclusion

I hope you all learned something from this challenge and had a good practice in Calculation groups. My advise for Calculation Groups is “Less is Better” to avoid complications in the report.

Thanks everyone who participated in this challenge because it is our members and non-members who make these initiatives the success.

For in-depth look into the solution, you can check out the Video in Youtube.

This is it from my side &

STAY TUNED FOR POTW 8!

POTW # 7 - Dynamic Report with Calculation Groups Final.pbix (48.7 MB)

[BrianJ]

All,

A major kudos to the hearty souls who navigated their way through this week’s Problem of the Week – definitely the most difficult one to date. A big thumbs up also to those who attempted this one but didn’t get all the way to a solution – hopefully even the attempt taught you a lot and made the final solution all the more resonant and instructive.

A huge thanks to this week’s leader @MudassirAli, who put together a really thoughtful, multifaceted problem that provided a great introduction (and more!) to the interesting and useful world of calculation groups. Hopefully, given his excellent breakdown in the write up and the video, you now have some additional tools in your DAX toolbox.

If you did complete this week’s problem and would like a sticker, please send your mailing address (NOT your email address) to me at brian.julius@enterprisedna.co. We’ll be back next week with a new Power Query problem led by Enterprise DNA expert @alexbadiu.

Thanks again for participating.

– Brian

[quantumudit]

Here goes my submission for the Week-07 and for this one I peeped multiple times to the solution video and learned a lot of things and about the red zones while using the Tabular editor.

Week07_Dynamic_Report_with_Calculation_Groups_DAX_Udit.pbix (49.3 MB)

Indeed an amazing problem and I would love to see more such DAX challenges related to Tabular editor and other external tools, as well.

With this solution, I dedicated my whole day to POTW and submitted Week-06, 07, and 08.

Lots of learning for the day.

Thanks a lot, @MudassirAli and @BrianJ for this amazing challenge

[BrianJ]

@quantumudit

Here goes my submission for the Week-07 and for this one I peeped multiple times to the solution video and learned a lot of things and about the red zones while using the Tabular editor.

Great work and definitely no shame at all in having to peep at the solution video for #7. I think @MudassirAli would admit that this problem ended up being more nuanced and difficult that he originally intended it to be, and I think it’s without question the toughest one to date.

I love that you’re taking the time to go back and work through the ones you missed. In addition to that, I think there’s also some real value in revisiting ones you completed. Particularly for the Power Query challenges, I’m finding that over time due to deliberate practice on POTW and forum responses, and reviewing the solutions of others that my own skill keeps increasing, and I can substantially improve on my submitted solutions from past problems. Thus the archive on POTW serves as a great periodic benchmark for how your skill is progressing.

With regard to challenges involving external tools, have to give that some more thought and discussion. Personally, I agree with you completely and I’m finding now that I write and debug almost all of my DAX within TE3. However, I also recognize that for many people, security prevents them from using these external tools. So a good compromise may be to show how these tools can be used to get to a quicker and/or more efficient solution, but set the problems that you don’t need to use them to complete that week’s round. Would be very interested to hear what you and others think of this.