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

Create data modole and Calculate percentage from two Tables

dofrancis3 — Tue, 08 Jul 2025 22:33:32 GMT

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

Re: Create data modole and Calculate percentage from two Tables

lbendlin — Tue, 08 Jul 2025 23:09:21 GMT

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().

Re: Create data modole and Calculate percentage from two Tables

FBergamaschi — Wed, 09 Jul 2025 05:50:37 GMT

Looks like cross posting?

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-percentage-from-two-Tables/m-p/4756825#M182121

Re: Create data modole and Calculate percentage from two Tables

v-dineshya — Wed, 09 Jul 2025 06:39:40 GMT

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

Re: Create data modole and Calculate percentage from two Tables

dofrancis3 — Wed, 09 Jul 2025 08:51:41 GMT

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

Re: Create data modole and Calculate percentage from two Tables

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

Is not this already solved?

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-percentage-from-two-Tables/m-p/4756825#M182121

Re: Create data modole and Calculate percentage from two Tables

dofrancis3 — Wed, 09 Jul 2025 09:27:48 GMT

Not yet the srtucture of data is different

Re: Create data modole and Calculate percentage from two Tables

dofrancis3 — Wed, 09 Jul 2025 10:13:06 GMT

Dear FBergamaschi please support

Re: Create data modole and Calculate percentage from two Tables

FBergamaschi — Wed, 09 Jul 2025 10:37:17 GMT

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

Re: Create data modole and Calculate percentage from two Tables

dofrancis3 — Wed, 09 Jul 2025 12:04:23 GMT

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

FBergamaschi

Re: Create data modole and Calculate percentage from two Tables

dofrancis3 — Wed, 09 Jul 2025 23:07:32 GMT

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.

Re: Create data modole and Calculate percentage from two Tables

dofrancis3 — Thu, 10 Jul 2025 06:30:52 GMT

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.

Re: Create data modole and Calculate percentage from two Tables

lbendlin — Thu, 10 Jul 2025 13:05:20 GMT

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

Re: Create data modole and Calculate percentage from two Tables

dofrancis3 — Thu, 10 Jul 2025 16:42:31 GMT

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