Forum Discussion
Problem creating Calculated Column with Multiple Tables
I have 2 tables, Table1 has all the people who work on the company. Table 2 has all the people who made sales on the last months and only this people (so i have repetitive people with a column of different periods (column period with 202203, 202204 and column sales 60%, 70% for the seller Pedro as example).
On table 2 i have the percentage that the seller receive that month (top 100).
My problem is that i want to show in a visual matrix all the people i have in the company and the percentages by month for each one, and the people that doesn't appear on Table2 should show automatically 100%.
Possible example:
Name Month Percentage for Sales
Pedro 202203 60
Martin 202203 100
Martin doesn't appear at Table2 and he should receive automatically 100
I thought on creating a calculate column but i believe it doesn't work because a have repetitive data on Table 2 and missing data too.
I probably can't change the tables from the origin, so i have to do it at Power BI
From the already thank you very much to everyone
2 Replies
Anonymous , Create a measure on table 2 , if the values is blank then make it 100
example
if(isblank([Percentage for Sales]),1, [Percentage for Sales])
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.Hello amitchandak, thanks you for your answer!
I tried to do that but the problem is that if there are months that a person (example Pedro) doesn't have sales % he doesn't appear on Table2
Example:
We have Pedro, Martin and Juliet.
Pedro and Martins always charge sales %, but Juliet never does it.
On Table2 we can see this:
Name Month Percentage for Sales
Pedro 202203 60
Pedro 202204 70
Pedro 202205 55
Martin 202203 100
Martin 202204 95
There isn’t Juliet there but i need to show in a visual like a matrix or a table this:
Name Month Percentage for Sales
Pedro 202203 60
Pedro 202204 70
Pedro 202205 55
Martin 202203 100
Martin 202204 95
Martin 202205 90
Juliet 202203 100
Juliet 202204 100
Juliet 202205 100
and probably with filters this (same period) :
Name Month Percentage for Sales
Pedro 202205 55
Martin 202205 90
Juliet 202205 100
Maybe now it's more clear what is my problem about.
Thanks you all, i always read the forum and learn a lot for you
