Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Schwurblini
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:

DateProductName etc
07.02.2022Atony
10.02.2022Aanne
15.02.2022Bfrank
16.02.2022Dmark
21.02.2022Csusan
22.02.2022Ajoe

 

Onboarding finished:

DateProductsName etc
07.02.2022product Acust tony
15.02.2022product Bcust frank
16.02.2022product Dcust mark
22.02.2022product Acust 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:
ProductspercentageCount of finishedrevenue etc
product A50%300 
product B70%500 
product C

30%

200

 

product D45%400 

 

Any idea how i can make these percentage values flexble?

 

Thanks a lot and best.

1 ACCEPTED SOLUTION
amitchandak
Super User
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]) )

View solution in original post

1 REPLY 1
amitchandak
Super User
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]) )

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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