cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

Percentage calculation between two different files and filterable by date

Hi All

So i have this problem with percentage calculation of successful onboardings of my customers. I want to calculate the percentage by products with dividing numbers from two different files. The problem is, that they are from different sources and do not share much in common besides a date stamp.

So i have two tables:

Onboardings started:

 Date Product Name etc 07.02.2022 A tony 10.02.2022 A anne 15.02.2022 B frank 16.02.2022 D mark 21.02.2022 C susan 22.02.2022 A joe

Onboarding finished:

 Date Products Name etc 07.02.2022 product A cust tony 15.02.2022 product B cust frank 16.02.2022 product D cust mark 22.02.2022 product A cust joe

Now to calculate the percentage by product i created an extra table:

Percentage =
SUMMARIZE(
'onboarding finished',
'onboarding finished'[Products]
)

In here i calculated another column:
Percentage Device 1 =
SWITCH (
TRUE (),
'Percentage'[Products] = "product A", CALCULATE(
DIVIDE( COUNT('Percentage'[Products]), COUNT(Onboardings started[product])), 'Onboarding finished'[Products] = "product A", Onboarding started[product] = "A"),
'Percentage'[Products] = "product B", CALCULATE(
DIVIDE( COUNT('Percentage'[Products]), COUNT(Onboardings started[product])), 'Onboarding finished'[Products] = "product B", Onboarding started[product] = "B"),
)

This gives me a breakdown of the percentage by product and with a relationship with Onboarding finished i can display it with products. The problem is it is not flexible and i cant show the different values each month, so fir example i cant see the percentage from january or february.

I would like to have a table like this:
 Products percentage Count of finished revenue etc product A 50% 300 product B 70% 500 product C 30% 200 product D 45% 400

Any idea how i can make these percentage values flexble?

Thanks a lot and best.

1 ACCEPTED SOLUTION
Super User

@Schwurblini , Either remove Product from product name in table 2. have that first

New column in Table 1

Product Name = "Product " & [Product]

Create a common Date table and product Table

Product = distinct(union(distinct(Table1[Product Name]),distinct(Table2[Product])))

Join Date and Product Dim with with both tables and use them in visual, slicer and measures

Create a mesure like = Divide(Count(Table[Product Name]), Count(Table2[Product]) )

Super User

@Schwurblini , Either remove Product from product name in table 2. have that first

New column in Table 1

Product Name = "Product " & [Product]

Create a common Date table and product Table

Product = distinct(union(distinct(Table1[Product Name]),distinct(Table2[Product])))

Join Date and Product Dim with with both tables and use them in visual, slicer and measures

Create a mesure like = Divide(Count(Table[Product Name]), Count(Table2[Product]) )

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors