topic Re: Problem creating Calculated Column with Multiple Tables in DAX Commands and Tips

Problem creating Calculated Column with Multiple Tables

Anonymous — Fri, 30 Sep 2022 15:19:25 GMT

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

Re: Problem creating Calculated Column with Multiple Tables

amitchandak — Sat, 01 Oct 2022 03:02:24 GMT

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.

Re: Problem creating Calculated Column with Multiple Tables

Anonymous — Sat, 01 Oct 2022 21:03:10 GMT

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