topic Re: Calculate percentage from two Tables in DAX Commands and Tips

Calculate percentage from two Tables

dofrancis3 — Tue, 08 Jul 2025 15:59:05 GMT

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

Re: Calculate percentage from two Tables

FBergamaschi — Tue, 08 Jul 2025 16:35:35 GMT

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

Re: Calculate percentage from two Tables

FBergamaschi — Tue, 08 Jul 2025 17:00:25 GMT

Recreated myself the model taking just a few rows

Result

Data Model

Tables

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

Re: Calculate percentage from two Tables

dofrancis3 — Tue, 08 Jul 2025 20:15:07 GMT

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?

Re: Calculate percentage from two Tables

FBergamaschi — Tue, 08 Jul 2025 20:39:48 GMT

Hi,

it depends of the meaning of the two columns

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

Perf % = DIVIDE( SUM( Actual[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

Re: Calculate percentage from two Tables

dofrancis3 — Tue, 08 Jul 2025 20:57:32 GMT

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

Categories = DISTINCT ( UNION( ALLNOBLANKROW( Actual[Category] ), ALLNOBLANKROW( Target[Category] ) ) )

Countries = DISTINCT ( UNION( ALLNOBLANKROW( Actual[Country] ), ALLNOBLANKROW( Target[Country] ) ) )

Re: Calculate percentage from two Tables

FBergamaschi — Tue, 08 Jul 2025 20:59:10 GMT

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

Re: Calculate percentage from two Tables

dofrancis3 — Tue, 08 Jul 2025 21:16:31 GMT

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

Re: Calculate percentage from two Tables

FBergamaschi — Wed, 09 Jul 2025 05:25:10 GMT

Go to Modeling -> New table

and input DAX

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

Re: Calculate percentage from two Tables

dofrancis3 — Wed, 09 Jul 2025 09:32:45 GMT

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: