Just a quick post on something that I recently had to remember how to do and thought would be useful to write down and share.
The Problem
I have a dataset which duplicates some values, something like this:
Here I’ve got two product lines that each have two narratives associated with them. I am required to SUM the price column for a totals rows in a table displaying the product lines, so that I get an output in my report something like this:
By default, SSRS will use a SUM expression for the ‘Price’ column (SUM(Fields!Price.Value)), but in this instance it will give the wrong answers (21.00, 41.98, 62.98) because the product lines are being doubled up by their narratives. Ordinarily you might break the narratives out into their own data set, but sometimes that is not possible. So how do I get the correct answer?
The Solution
The expressions in the ‘Price’ column should be set to:
=SUM(IIF(Fields!NarrativeNum.Value = 1, Fields!Price.Value, 0))
This expression checks for the value of NarrativeNum on the current row and only SUMs the Price value if NarrativeNum == 1, otherwise we substitute a 0. In effect, the SUM sees our dataset like this:
Summary
We’ve seen how we can use a combination of functions to prevent SSRS from counting duplicate values. This is a simple technique but can be very useful.
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr