Create data modole and Calculate percentage from two Tables

[dofrancis3]

Dear estimated Colleagues,

Please i reachout to you because 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 or/and year.
Could you help me with DAX Mesure and the way to create data model thank you in advance for you support. 

Table Actual

Country	Year	DT1	DT2	IP	MCV
DRC	2020	20	7	84	35
DRC	2021	20	7	10	11
DRC	2022	15	5	0	9
DRC	2020	25	5	30	40
Nigeria	2021	6	7	40	33
Nigeria	2020	2	1	6	4
Nigeria	2022	40	15	5	55
Nigeria	2022	11	6	2	9
Mauritania	2020	69	6	8	12
Mauritania	2021	21	37	4	0
Mauritania	2022	18	12	22	3
Mauritania	2023	7	13	10	9

Table Target

Country	Category	Target
DRC	DT1	120
DRC	DT2	45
DRC	IP	53
DRC	MCV	89
Nigeria	DT1	136
Nigeria	DT2	23
Nigeria	IP	180
Nigeria	MCV	70
Mauritania	DT1	93
Mauritania	DT2	77
Mauritania	IP	55
Mauritania	MCV	39

[lbendlin · Accepted solution]

First step is to bring your data into a usable format by unpivoting it

Then you can decide if you want to use a data model or TREATAS  (which is easier in your example)

and then you can decide which of these values make sense and which need to be suppressed with BLANK().

[FBergamaschi · Accepted solution]

You do not have it but if you unpivot you do have it like @lbendlin  outlined

I confrim his numbers and to let you check that this is correct I oncluded Actual and Target calculation, your 121 % is not clear how you calculated that as it seems you sum the actual of all categories in a year and in a country (DRC 2020, total actual 146 considering all categories) and then compare it to the target of one single category in the sam country (DRC DT1 target 120) so it is unclear what you want to do. Also, the target to not have any year, are they the same every year?

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 · Accepted solution]

Dear @lbendlin thank you so much that's exactly what I was looking for.

@FBergamaschi 

[lbendlin · Accepted solution]

You can create a group for these scenarios, or if the granularity is not needed you can aggregate your data already in Power Query.

[FBergamaschi · Accepted solution]

You do not have it but if you unpivot you do have it like @lbendlin  outlined

I confrim his numbers and to let you check that this is correct I oncluded Actual and Target calculation, your 121 % is not clear how you calculated that as it seems you sum the actual of all categories in a year and in a country (DRC 2020, total actual 146 considering all categories) and then compare it to the target of one single category in the sam country (DRC DT1 target 120) so it is unclear what you want to do. Also, the target to not have any year, are they the same every year?

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]

Dear @FBergamaschi  thank you for your support but i have an issue:

1). Should i unpivot all colomns?

2). How can i Summarize (combine/merge) values of "DT1 and DT2" to DT

That means i will have only "DT", "IP" and "MCV".

Thank you for your feed-back.

[v-dineshya]

Hi @dofrancis3 ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @lbendlin , Thank you for your prompt response.

Hi @dofrancis3 , If @lbendlin , response has resolved your issue, Please mark his response as  “Accept it as a solution”  to assist other community members in resolving similar issues more efficiently.

Regards,

Dinesh

[FBergamaschi]

Looks like cross posting?

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-percentage-from-two-Tables...

[lbendlin · Accepted solution]

First step is to bring your data into a usable format by unpivoting it

Then you can decide if you want to use a data model or TREATAS  (which is easier in your example)

and then you can decide which of these values make sense and which need to be suppressed with BLANK().

[dofrancis3 · Accepted solution]

Dear @lbendlin thank you so much for your support it's perfectly work

[dofrancis3]

Dear @lbendlin thank you for your support but i have an issue

1). Should i unpivoting all the colomns?

2). How can i Summarize (combine/merge) values of "DT1 and DT2" to DT

That means i will have only "DT", "IP" and "MCV".

Thank you for your feed-back.

[lbendlin · Accepted solution]

You can create a group for these scenarios, or if the granularity is not needed you can aggregate your data already in Power Query.

[dofrancis3 · Accepted solution]

Dear @lbendlin thank you so much that's exactly what I was looking for.

@FBergamaschi 

[dofrancis3]

Dear @lbendlin Thank you for your reply but as you can see the result isn't good apparently the formula isn't good the rusult should be the same (the formula is DT1+DT2+IP+MCV/ TARGET)

Please support 

@FBergamaschi 

[FBergamaschi]

Is not this already solved?

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-percentage-from-two-Tables...

[dofrancis3]

Not yet the srtucture of data is different 

[dofrancis3]

Dear @FBergamaschi please support