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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dofrancis3
Resolver I
Resolver I

Calculate percentage from two Tables

Dear Colleagues,

Please i need your help !

As you can see in the screenshoot below i have Two tables and I would like to calculate the percentage of achievement by category and by country.

Could you help me with DAX Mesure

dofrancis3_1-1751990256057.png

 

1 ACCEPTED SOLUTION

Recreated myself the model taking just a few rows

 

Result

 

result.png

Data Model

model.png

 

Tables

 

Tables.png

DAX measure code

 

Perf % = DIVIDE( SUM( Actual[Actual] ), SUM ( Target[Target] ) )
 
Dimensions additional tables to create the data model
 
Categories = DISTINCT ( UNION( ALLNOBLANKROW( Actual[Category] ), ALLNOBLANKROW( Target[Category] ) ) )
 
Countries = DISTINCT ( UNION( ALLNOBLANKROW( Actual[Country] ), ALLNOBLANKROW( Target[Country] ) ) )
 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

View solution in original post

9 REPLIES 9
dofrancis3
Resolver I
Resolver I

Dear @FBergamaschi thank you for your replay but please I forgot to mention that there are two columns of actual please how can i proced in this case?

dofrancis3_0-1752005092027.png

 

Hi,

it depends of the meaning of the two columns

 

If the Actual becomes the sum of the two columns the DAX becomes

 

Perf % = DIVIDESUMActual[Actual 1] ) + SUM( Actual[Actual 2] ), SUM ( Target[Target] ) )

 

Otherwise please explain how from those two column one should obtain the actual

 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

 

 

 

So, that means whatever the number of "Actual column" I’ll have the data model below should remain the same right?

Categories = DISTINCT ( UNIONALLNOBLANKROWActual[Category] ), ALLNOBLANKROWTarget[Category] ) ) )
 
Countries = DISTINCT ( UNIONALLNOBLANKROWActual[Country] ), ALLNOBLANKROWTarget[Country] ) ) )

Yes, right

 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

So, how can i créate these two tables?  

dofrancis3_0-1752009212257.png

 

Go to Modeling -> New table

 

and input DAX

 

Categories = DISTINCT ( UNIONALLNOBLANKROWActual[Category] ), ALLNOBLANKROWTarget[Category] ) ) )
 
Countries = DISTINCT ( UNIONALLNOBLANKROWActual[Country] ), ALLNOBLANKROWTarget[Country] ) ) )
 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

The structure of data is different we don't have Category name in the Actual table (see data below).

So, how can i change the DAX below:

dofrancis3_2-1752053463396.png

 

dofrancis3_1-1752053419905.png

 

 

FBergamaschi
Solution Sage
Solution Sage

Hi,

you need to create a data model, the DAX is very simple. Please can you paste the tables in a way that I can copy them, so i send you back the model and the DAX formula.

 

Best

 

 

Recreated myself the model taking just a few rows

 

Result

 

result.png

Data Model

model.png

 

Tables

 

Tables.png

DAX measure code

 

Perf % = DIVIDE( SUM( Actual[Actual] ), SUM ( Target[Target] ) )
 
Dimensions additional tables to create the data model
 
Categories = DISTINCT ( UNION( ALLNOBLANKROW( Actual[Category] ), ALLNOBLANKROW( Target[Category] ) ) )
 
Countries = DISTINCT ( UNION( ALLNOBLANKROW( Actual[Country] ), ALLNOBLANKROW( Target[Country] ) ) )
 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.