Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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 2
amitchandak
Super User
Super User

@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.

Anonymous
Not applicable

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.