There are several ways of distributing values. One way is to use a factor mapped to rows in a table to distribute them accordingly. Let’s dive into how to do this in Power BI with DAX.
The question
One of my clients had the following question:
I have data about projects with business partners. I can have multiple partners participating in one or more Projects.
How can I create a Power BI Report in which I can see the amount of money my partners are spending per period?
Data-Model
I created a small dataset to demonstrate this case:
The data model in Power BI resembles the figure above:
The relationship between Project and Spending and Partner and Project must be bidirectional, as I must filter the data in both ways. For example, when I need to filter the projects by Partner.
The planned outcome
Let’s look at one Project to see what the outcome should be:
We have 125’600 of Spending for the Project "Audio".
Three partners are active in this Project:
- Contoso, Ltd.
- Adventure Works
- Proseware, Inc.
When we map the factor to the spending for each partner, we get the following result:
The main target is to see how much each partner has spent overall or on one project.
Thoughts and solution
Well, the first thought was to create a Measure. Of course, I did.
I would take into consideration another solution I realized for another client, which I documented in the following article:
When a Count goes mad or how to count Orders over time in DAX
But I soon realized that this was no viable option.
I need one Measure to calculate the result, as I don’t have any grouping or categories I can use to split my result into multiple Measures.
My first approach for the Measure was to use the relationships to multiply the spending per project for each partner.
The first results were complex and relatively slow.
So, I changed my approach and started creating a table with the multiplication of the spending per project by the corresponding factor per partner.
The target table should look like this:
Then I would be able to add relationships to the data model:
With this table, I would be able to add a simple Measure summing the "Spending by Factor" column.
To create the target table, I used Dax Studio.
The first table I create is to get the Spending for each Project:
DEFINE
VAR F =
SELECTCOLUMNS(
'Project and Spending'
,"ProjectID", 'Project and Spending'[ProjectID] & ""
,"Spending", 'Project and Spending'[Spending]
)
EVALUATE
F
The result of the Query looks like this:
The second Query is to get the Factory for each Partner and Project:
DEFINE
VAR C =
SELECTCOLUMNS(
'Partners and Projects'
,"PartnerID", 'Partners and Projects'[PartnerID]
,"ProjectID", 'Partners and Projects'[ProjectID] & ""
,"Factor", 'Partners and Projects'[Factor]
)
EVALUATE
C
And the result of this query is the following:
The last step is to combine these two tables and to multiplicate the Spending for each Project with the Factor for each Partner.
To achieve this, I use the NATURALLEFTOUTERJOIN() function.
With this function, each row of the first table is matched with each row of the second table through the column(s) with the same name.
Then, I use the ADDCOLUMNS() functions to calculate the Spending multiplicated by the factor for each Partner/Project combination.
Here is the complete query:
DEFINE
VAR C = SELECTCOLUMNS(
'Partners and Projects'
,"PartnerID", 'Partners and Projects'[PartnerID]
,"ProjectID", 'Partners and Projects'[ProjectID] & ""
,"Factor", 'Partners and Projects'[Factor]
)
VAR F = SELECTCOLUMNS(
'Project and Spending'
,"ProjectID", 'Project and Spending'[ProjectID] & ""
,"Spending", 'Project and Spending'[Spending]
)
EVALUATE
ADDCOLUMNS(
NATURALLEFTOUTERJOIN(F, C)
,"Spending by Factor", [Spending] * [Factor]
)
The (truncated) result is the following:
After some checking of the result, I can take this query and translate it into the DAX Expression for the target table in Power Bi:
Project per Partner =
VAR F =
SELECTCOLUMNS(
'Project and Spending'
,"ProjectID", 'Project and Spending'[ProjectID] & ""
,"Spending", 'Project and Spending'[Spending]
)
VAR C =
SELECTCOLUMNS(
'Partners and Projects'
,"PartnerID", 'Partners and Projects'[PartnerID]
,"ProjectID", 'Partners and Projects'[ProjectID] & ""
,"Factor", 'Partners and Projects'[Factor]
)
RETURN
ADDCOLUMNS(
NATURALLEFTOUTERJOIN(F, C)
,"Spending by Factor", [Spending] * [Factor]
)
Now, I can create the Relationships, and all is done.
The final Measure is simple:
Sum of Spending by Partner = SUM('Project per Partner'[Spending by Factor])
This Measure can be added to a Visual to show the Result:
As you can see, the Spending is correctly distributed by the partners, and the total add-up to the numbers in Figure 3.
Adding Time
As you might have noticed, one of my Client’s questions was: Spending per Period.
My example had no time axis until now.
I have to modify my data to have a clean data model without m:n Relationships.
For this, I create a new table with only the projects and expand the table with the Spending with a Date column.
Of course, I add a Date table to the Model.
The resulting Data Model looks like this:
The DAX expression to create the Project per Partner table is slightly different.
Project per Partner =
VAR F =
SELECTCOLUMNS(
'Spending'
,"Date", 'Spending'[Date]
,"ProjectID", 'Spending'[ProjectID] & ""
,"Spending", 'Spending'[Spending]
)
VAR C =
SELECTCOLUMNS(
'Partners and Projects'
,"PartnerID", 'Partners and Projects'[PartnerID]
,"ProjectID", 'Partners and Projects'[ProjectID] & ""
,"Factor", 'Partners and Projects'[Factor]
)
RETURN
ADDCOLUMNS(
NATURALLEFTOUTERJOIN(F, C)
,"Spending by Factor", [Spending] * [Factor]
)
I only must switch to the Spending table and add the Date column to the expression.
The Data can be filtered by Month to get the required results:
The Measure "Sum of Spending by Partner" doesn’t need any change.
In addition, the Data Model is now cleaner, as the Partners and the Projects are in separate tables without other columns.
Such a clean Data Model is essential for building a more straightforward solution.
Conclusion
Learning how to use tables in DAX is an important lesson. It opens many possibilities to solve complex problems or challenges.
The use of DAX Studio is an essential factor in solving such challenges.
The main lesson I learned here was to refrain from trying to use previous solutions to solve challenges.
Experience is good. But only to learn what I can do and what is good or bad.
Be aware that I didn’t write "What I can do and what not". Every challenge poses unique questions and needs individual and innovative solutions.
Sometimes you can reuse previous solutions. And these are the "simpler challenges".
All other challenges are interesting ones.
References
The data has been derived from the Contoso Dataset.
The ContosoRetailDW sample dataset is available for free download from Microsoft here.
The Contoso Data can be freely used under the MIT License, as described here.
If you appreciate my work, feel free to support me through
Or scan this QR Code:
Any support is greatly appreciated.
Thank you.