Calculate percentage from two Tables

[dofrancis3]

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]

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?

[FBergamaschi]

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

[dofrancis3]

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] ) ) )

[FBergamaschi]

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

[dofrancis3]

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

[FBergamaschi]

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

[dofrancis3]

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:

[FBergamaschi]

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

[FBergamaschi · Accepted solution]

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