Forum Discussion
pierredelareux
Helper I
Helper ICalculate Grouped Percentage
Hi,
Please find bellow a screen-shot of my table.
I would like to create a chart that gives me the reprensatation of the followings fields :
- % of (UR/Non_Exclus) by Territoire by month
- % of (UR/Non_Exclus) by Site by month
I've tried to group, to calculate and summurize, didn't reach my goal.
Thanks for your help
Pierre
9 Replies
- Phil_Seamark
Microsoft Employee
This should be pretty straight forward, Any chance you can provide a text version of your data rather than an image. It makes building suggestions for measures much easier. :)
pierredelareuxHere
Thanks :-)
Nom Station Site Territoire DATE EXCLUS NON_EXCLUS UR AACHBO-SCVAL SCVAL CCO 25/02/2016 00:00 0 1 0 AACHBO-SCVAL SCVAL CCO 11/03/2016 00:00 0 1 1 AACHBO-SCVAL SCVAL CCO 18/04/2016 00:00 0 1 1 AACHBO-SCVAL SCVAL CCO 30/05/2016 00:00 0 1 1 AACHBO-SCVAL SCVAL CCO 06/06/2016 00:00 0 1 1 AACHBO-SCVAL SCVAL CCO 16/02/2017 00:00 0 1 0 AACHBO-SCVAL SCVAL CCO 27/02/2017 00:00 0 1 1 AACHBO-SCVAL SCVAL CCO 14/03/2017 00:00 0 1 1 ABBADIA-BRUNE BRUNE CIF 31/05/2016 00:00 1 0 0 ABBADIA-BRUNE BRUNE CIF 24/06/2016 00:00 1 0 0 ABBADIA-BRUNE BRUNE CIF 29/11/2016 00:00 1 0 0 ABBADIA-BRUNE BRUNE CIF 12/12/2016 00:00 1 0 0 ABBANEY-ENDEL ENDEL SPA 30/09/2016 00:00 0 1 1 ABBANEY-ENDEL ENDEL SPA 27/10/2016 00:00 0 1 1 ABBANEY-ENDEL ENDEL SPA 30/11/2016 00:00 0 1 1 ABBANEY-ENDEL ENDEL SPA 15/12/2016 00:00 0 1 1 ABBANEY-ENDEL ENDEL SPA 28/02/2017 00:00 0 1 1 ABBANEY-ENDEL ENDEL SPA 31/03/2017 00:00 0 1 1 ABBANEY-ENDEL ENDEL SPA 03/04/2017 00:00 0 1 0 ABDALLA-SONOR SONOR SOM 16/05/2016 00:00 0 1 0 ABDALLA-SONOR SONOR SOM 20/05/2016 00:00 0 1 0 ABDALLA-SONOR SONOR SOM 09/08/2016 00:00 0 1 0 ABDALLA-SONOR SONOR SOM 19/10/2016 00:00 0 1 1 ABDALLA-SONOR SONOR SOM 20/12/2016 00:00 0 1 0 ABDALLA-SONOR SONOR SOM 28/02/2017 00:00 0 1 0 ABDALLA-SONOR SONOR SOM 01/03/2017 00:00 0 1 1 ABDALLA-SONOR SONOR SOM 06/04/2017 00:00 0 1 0 ABDELFK1-VECCH VECCH RAI 24/03/2016 00:00 1 0 0 ABDELFK1-VECCH VECCH RAI 29/06/2016 00:00 1 0 0 ABDELFK1-VECCH VECCH RAI 03/07/2016 00:00 1 0 0 ABDELFK1-VECCH VECCH RAI 21/10/2016 00:00 1 0 0 ABDELFK1-VECCH VECCH RAI 08/11/2016 00:00 1 0 0 ABDELFK1-VECCH VECCH RAI 16/12/2016 00:00 1 0 0 ABDELFK1-VECCH VECCH RAI 31/01/2017 00:00 1 0 0 ABDELRB-PITRA PITRA CIF 28/02/2017 00:00 0 1 1 ABDELRB-PITRA PITRA CIF 06/03/2017 00:00 0 1 1 ABEILL-SIERA SIERA CRA 29/02/2016 00:00 1 0 0 ABEILL-SIERA SIERA CRA 31/03/2016 00:00 1 0 0 ABEILL-SIERA SIERA CRA 28/04/2016 00:00 1 0 0 ABEILL-SIERA SIERA CRA 02/05/2016 00:00 1 0 0 ABEILL-SIERA SIERA CRA 28/06/2016 00:00 1 0 0 ABEILL-SIERA SIERA CRA 30/09/2016 00:00 1 0 0 ABEILL-SIERA SIERA CRA 24/10/2016 00:00 1 0 0 ABEILL-SIERA SIERA CRA 28/11/2016 00:00 1 0 0 ABEILL-SIERA SIERA CRA 19/12/2016 00:00 1 0 0 ABEILL-SIERA SIERA CRA 23/03/2017 00:00 1 0 0 ABRAMSA-2EDCRB EDCRB CRB 10/05/2016 00:00 0 1 0 ABRAMSA-2EDCRB EDCRB CRB 01/06/2016 00:00 0 1 0 ACQUIS-2PEGOM PEGOM CMM 29/06/2016 00:00 0 1 1 ACUNAS-2CHILA CHILA MAC 19/04/2016 00:00 0 1 1 ACUNAS-2CHILA CHILA MAC 13/05/2016 00:00 0 1 1 ACUNAS-2CHILA CHILA MAC 22/06/2016 00:00 0 1 1 ACUNAS-2CHILA CHILA MAC 30/09/2016 00:00 0 1 0 ACUNAS-2CHILA CHILA MAC 07/10/2016 00:00 0 1 0 ACUNAS-2CHILA CHILA MAC 17/11/2016 00:00 0 1 0 ACUNAS-2CHILA CHILA MAC 14/12/2016 00:00 0 1 0 ACUNAS-2CHILA CHILA MAC 10/01/2017 00:00 0 1 1 ACUNAS-2CHILA CHILA MAC 28/02/2017 00:00 0 1 1 ACUNAS-2CHILA CHILA MAC 22/03/2017 00:00 0 1 1 ADAMCIM-2SGSVK SGSVK SVK 19/10/2016 00:00 0 1 1 ADAMCIM-2SGSVK SGSVK SVK 15/11/2016 00:00 0 1 1 ADAMCIM-2SGSVK SGSVK SVK 15/12/2016 00:00 0 1 1 ADAMCIM-2SGSVK SGSVK SVK 31/01/2017 00:00 0 1 1 ADAMCIM-2SGSVK SGSVK SVK 28/02/2017 00:00 0 1 1 ADAMCIM-2SGSVK SGSVK SVK 31/03/2017 00:00 0 1 1 ADAMCIM-SGSVK SGSVK SVK 20/06/2016 00:00 0 1 1 ADAMCIM-SGSVK SGSVK SVK 30/06/2016 00:00 0 1 1 ADAMCIM-SGSVK SGSVK SVK 01/07/2016 00:00 0 1 0 ADAMCIM-SGSVK SGSVK SVK 24/08/2016 00:00 0 1 0 ADAMCIM-SGSVK SGSVK SVK 30/09/2016 00:00 0 1 1 ADAMCIM-SGSVK SGSVK SVK 10/10/2016 00:00 0 1 1 ADAMF-SGICO SGICO ICO 29/02/2016 00:00 0 1 0 ADAMF-SGICO SGICO ICO 28/06/2016 00:00 0 1 0 ADAMF-SGICO SGICO ICO 17/10/2016 00:00 0 1 0 ADAMF-SGICO SGICO ICO 10/11/2016 00:00 0 1 0 ADAMF-SGICO SGICO ICO 23/12/2016 00:00 0 1 1 ADAMF-SGICO SGICO ICO 16/03/2017 00:00 0 1 0 ADAMJ1-ISTRE ISTRE CMM 29/02/2016 00:00 0 1 0 ADAMJ1-ISTRE ISTRE CMM 29/03/2016 00:00 0 1 1 ADAMJ1-ISTRE ISTRE CMM 28/04/2016 00:00 0 1 0 ADAMJ1-ISTRE ISTRE CMM 11/05/2016 00:00 0 1 0 ADAMJ1-ISTRE ISTRE CMM 17/05/2016 00:00 0 1 1 ADAMJ1-ISTRE ISTRE CMM 24/06/2016 00:00 0 1 1 ADAMJ1-ISTRE ISTRE CMM 23/08/2016 00:00 0 1 0 ADAMJ1-ISTRE ISTRE CMM 30/09/2016 00:00 0 1 1 ADAMJ1-ISTRE ISTRE CMM 12/10/2016 00:00 0 1 1 ADAMJ1-ISTRE ISTRE CMM 25/11/2016 00:00 0 1 0 ADAMJ1-ISTRE ISTRE CMM 15/12/2016 00:00 0 1 1 ADAMJ1-ISTRE ISTRE CMM 21/02/2017 00:00 0 1 1 ADAMJ1-ISTRE ISTRE CMM 27/03/2017 00:00 0 1 0 ADAMK1-PRTCL PRTCL TER 21/03/2017 00:00 0 1 0 ADRIAN-2SARRE SARRE CES 01/03/2016 00:00 1 0 0 ADRIAN-2SARRE SARRE CES 01/04/2016 00:00 1 0 0 ADRIAN-2SARRE SARRE CES 26/04/2016 00:00 1 0 0 ADRIAN-2SARRE SARRE CES 13/05/2016 00:00 1 0 0 ADRIAN-2SARRE SARRE CES 05/10/2016 00:00 1 0 0 ADRIAN-2SARRE SARRE CES 14/11/2016 00:00 1 0 0 ADRIAN-2SARRE SARRE CES 20/02/2017 00:00 1 0 0 ADRIAN-2SARRE SARRE CES 20/03/2017 00:00 1 0 0 ADSHADT-CGSGC CGSGC SGI 25/03/2016 00:00 0 1 0 AERTSB-BEL04 BEL04 BEL 30/09/2016 00:00 0 1 0 AERTSB-BEL04 BEL04 BEL 15/12/2016 00:00 0 1 1 AFONSO-PFSQF PFSQF RAI 25/01/2017 00:00 0 1 1 AGGOURH-GTRAG GTRAG MAR 28/03/2016 00:00 0 1 0 AGGOURH-GTRAG GTRAG MAR 25/04/2016 00:00 0 1 1 AGGOURH-GTRAG GTRAG MAR 30/05/2016 00:00 0 1 1 AGGOURH-GTRAG GTRAG MAR 24/06/2016 00:00 0 1 1 AGGOURH-GTRAG GTRAG MAR 29/08/2016 00:00 0 1 1 AGGOURH-GTRAG GTRAG MAR 15/10/2016 00:00 0 1 0 AGGOURH-GTRAG GTRAG MAR 25/10/2016 00:00 0 1 1 AGGOURH-GTRAG GTRAG MAR 29/11/2016 00:00 0 1 1 AGGOURH-GTRAG GTRAG MAR 23/12/2016 00:00 0 1 0 AGGOURH-GTRAG GTRAG MAR 30/01/2017 00:00 0 1 1 AGGOURH-GTRAG GTRAG MAR 02/02/2017 00:00 0 1 0 AGGOURH-GTRAG GTRAG MAR 27/02/2017 00:00 0 1 0 AGGOURH-GTRAG GTRAG MAR 29/03/2017 00:00 0 1 1 AGHA-SEBRE SEBRE SOM 20/04/2016 00:00 0 1 1 AGNESJ1-2SJGEC SJGEC CIF 22/02/2017 00:00 0 1 0 AGNESJ1-2SJGEC SJGEC CIF 28/03/2017 00:00 0 1 1 AGNESJ1-SJGEC SJGEC CIF 24/02/2016 00:00 1 0 0 AGNESJ1-SJGEC SJGEC CIF 17/03/2016 00:00 1 0 0 AGNESJ1-SJGEC SJGEC CIF 26/04/2016 00:00 1 0 0 AGNESJ1-SJGEC SJGEC CIF 11/05/2016 00:00 1 0 0 AGNESJ1-SJGEC SJGEC CIF 30/05/2016 00:00 1 0 0 AGNESJ1-SJGEC SJGEC CIF 22/06/2016 00:00 1 0 0 AGNESJ1-SJGEC SJGEC CIF 30/09/2016 00:00 1 0 0 AGNESJ1-SJGEC SJGEC CIF 14/10/2016 00:00 1 0 0 AGNESJ1-SJGEC SJGEC CIF 30/11/2016 00:00 1 0 0 AGNESJ1-SJGEC SJGEC CIF 15/12/2016 00:00 1 0 0 AIGLONA-LACHA LACHA SPA 28/02/2017 00:00 0 1 1 AIGLONA-LACHA LACHA SPA 23/03/2017 00:00 0 1 1 AIRAUL-ECOLI ECOLI LCR 30/09/2016 00:00 0 1 0 AITHAMZ-CRMAR CRMAR RAI 15/11/2016 00:00 1 0 0 AITHAMZ-CRMAR CRMAR RAI 03/12/2016 00:00 1 0 0 AITHAMZ-CRMAR CRMAR RAI 07/01/2017 00:00 1 0 0 AITHAMZ-CRMAR CRMAR RAI 27/02/2017 00:00 1 0 0 AITHAMZ-CRMAR CRMAR RAI 13/03/2017 00:00 1 0 0 ALAOUIM1-GTRFE GTRFE MAR 12/08/2016 00:00 0 1 1 ALAOUIM1-GTRFE GTRFE MAR 30/09/2016 00:00 0 1 0 ALAOUIM1-GTRFE GTRFE MAR 04/10/2016 00:00 0 1 0 ALAOUIM1-GTRFE GTRFE MAR 21/12/2016 00:00 0 1 1 ALAOUIM1-GTRFE GTRFE MAR 04/01/2017 00:00 0 1 0 ALAOUIM1-GTRFE GTRFE MAR 20/02/2017 00:00 0 1 1 ALAOUIM-GTRTA GTRTA MAR 30/08/2016 00:00 0 1 1 ALAOUIM-GTRTA GTRTA MAR 30/09/2016 00:00 0 1 0 ALAOUIM-GTRTA GTRTA MAR 14/10/2016 00:00 0 1 0 ALAOUIM-GTRTA GTRTA MAR 22/11/2016 00:00 0 1 0 ALAOUIM-GTRTA GTRTA MAR 31/01/2017 00:00 0 1 1 ALAOUIM-GTRTA GTRTA MAR 15/03/2017 00:00 0 1 0 ALARCOA-2CHILA CHILA MAC 20/05/2016 00:00 0 1 0 ALARCOA-2CHILA CHILA MAC 18/10/2016 00:00 0 1 0 ALARCOA-2CHILA CHILA MAC 19/12/2016 00:00 0 1 1 ALARCOA-2CHILA CHILA MAC 23/01/2017 00:00 0 1 0 ALARCOA-2CHILA CHILA MAC 07/02/2017 00:00 0 1 0 ALARCOA-2CHILA CHILA MAC 30/03/2017 00:00 0 1 1 ALASIAV-SGGBR SGGBR GBR 04/05/2016 00:00 0 1 0 ALAURE-2SCVAL SCVAL CCO 03/04/2017 00:00 0 1 1 ALAVOI-SANTE SANTE CNR 24/02/2016 00:00 0 1 0 ALAVOI-SANTE SANTE CNR 23/03/2016 00:00 0 1 1 ALAVOI-SANTE SANTE CNR 11/04/2016 00:00 0 1 1 ALAVOI-SANTE SANTE CNR 05/12/2016 00:00 0 1 0 ALAVOI-SANTE SANTE CNR 13/03/2017 00:00 0 1 0 ALBERT-2SGTOI SGTOI TOI 24/02/2016 00:00 0 1 0 ALBERT-2SGTOI SGTOI TOI 24/03/2016 00:00 0 1 0 ALBERT-2SGTOI SGTOI TOI 29/04/2016 00:00 0 1 1 ALBERT-2SGTOI SGTOI TOI 20/05/2016 00:00 0 1 0 ALBERT-2SGTOI SGTOI TOI 10/06/2016 00:00 0 1 0 ALBERT-2SGTOI SGTOI TOI 13/12/2016 00:00 0 1 0 ALBERT-2SGTOI SGTOI TOI 27/02/2017 00:00 0 1 0 ALCOCKA-CGSGC CGSGC SGI 19/12/2016 00:00 0 1 1 ALCOCKA-CGSGC CGSGC SGI 17/02/2017 00:00 0 1 1 ALCOCKA-CGSGC CGSGC SGI 31/03/2017 00:00 0 1 1 ALCOCKA-CGSGC CGSGC SGI 04/04/2017 00:00 0 1 1 ALESSIP-2BRUNE BRUNE CIF 24/02/2016 00:00 0 1 0 ALESSIP-2BRUNE BRUNE CIF 24/03/2016 00:00 0 1 1 ALESSIP-2BRUNE BRUNE CIF 06/04/2016 00:00 0 1 1 ALESSIP-2BRUNE BRUNE CIF 12/05/2016 00:00 0 1 1 ALESSIP-2BRUNE BRUNE CIF 17/05/2016 00:00 0 1 1 ALESSIP-2BRUNE BRUNE CIF 31/05/2016 00:00 0 1 1 ALESSIP-2BRUNE BRUNE CIF 20/06/2016 00:00 0 1 0 ALESSIP-2BRUNE BRUNE CIF 30/08/2016 00:00 0 1 1 ALESSIP-2BRUNE BRUNE CIF 30/09/2016 00:00 0 1 0 ALESSIP-2BRUNE BRUNE CIF 18/10/2016 00:00 0 1 0 ALESSIP-2BRUNE BRUNE CIF 22/11/2016 00:00 0 1 1 ALESSIP-2BRUNE BRUNE CIF 01/03/2017 00:00 0 1 0 ALESSIP-2BRUNE BRUNE CIF 31/03/2017 00:00 0 1 1 ALEXANA1-SGGAB SGGAB GAB 02/03/2016 00:00 0 1 0 ALEXANA1-SGGAB SGGAB GAB 24/03/2016 00:00 0 1 1 ALEXANA1-SGGAB SGGAB GAB 12/04/2016 00:00 0 1 1 ALEXANA1-SGGAB SGGAB GAB 31/05/2016 00:00 0 1 0 ALEXANA1-SGGAB SGGAB GAB 28/06/2016 00:00 0 1 0 ALEXANA1-SGGAB SGGAB GAB 30/09/2016 00:00 0 1 0 ALEXANA1-SGGAB SGGAB GAB 19/10/2016 00:00 0 1 0 ALISONN-SGMAD SGMAD MAD 26/02/2016 00:00 0 1 0 ALLAIRB1-SMALO SMALO CCO 31/05/2016 00:00 0 1 1 ALLAIRB1-SMALO SMALO CCO 01/07/2016 00:00 0 1 1 ALLAIRB1-SMALO SMALO CCO 05/07/2016 00:00 0 1 1 ALLAIRB1-SMALO SMALO CCO 30/08/2016 00:00 0 1 1 ALLAIRB1-SMALO SMALO CCO 30/09/2016 00:00 0 1 1 ALLAIRB1-SMALO SMALO CCO 01/10/2016 00:00 0 1 1
- Phil_Seamark
That is very helpful. And based on that sample data do you have a mock up of what the metrics should show? Even just for a particular Territoire/month :)
v-haibl-msftYou can create a measure with following formula.
Ps: I named the table tas Table15.
UR/Non_Exclus = CALCULATE ( DIVIDE ( SUM ( Table15[UR] ), SUM ( Table15[NON_EXCLUS] ) ) )
Best Regards,
Herbert- pierredelareux
Ok
I'll try your solution v-haibl-msft.
Plese find the datas and expected result here --> https://we.tl/y3BpsmmEcC
Thanks
Pierre
- pierredelareux
Hi v-haibl-msft,
It's not perfect because your formula calculate the Nen-Exclus sum for each month.
in my data Non-Exclus are defin once for all.
Thanks for your feedback.
Pierre
