Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
pierredelareux
Helper I
Helper I

Calculate Grouped Percentage

‎04-13-2017 11:37 PM

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

 

screen.jpg

 

I've tried to group, to calculate and summurize, didn't reach my goal.

 

Thanks for your help

 

Pierre

Message 1 of 10
2,276 Views
0
9 REPLIES 9
v-haibl-msft
Microsoft Employee
Microsoft Employee

‎04-17-2017 01:29 AM

@pierredelareux

 

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

Calculate Grouped Percentage_1.jpg

 

Best Regards,
Herbert

Message 7 of 10
2,224 Views
0
pierredelareux
Helper I
Helper I
In response to v-haibl-msft

‎04-17-2017 11:45 PM

Ok

I'll try your solution @v-haibl-msft.

 

Plese find the datas and expected result here --> https://we.tl/y3BpsmmEcC

 

Thanks

Pierre

Message 8 of 10
2,198 Views
0
pierredelareux
Helper I
Helper I
In response to pierredelareux

‎04-20-2017 01:21 AM

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

Message 9 of 10
2,161 Views
0
v-haibl-msft
Microsoft Employee
Microsoft Employee
In response to pierredelareux

‎04-24-2017 01:39 AM

@pierredelareux

 

Maybe you’d like to sum Non_Exclus just by Territoire or by Site? Here is the solution.

 

UR/Non_Exclus =
DIVIDE (
    SUM ( Table15[UR] ),
    CALCULATE (
        SUM ( Table15[NON_EXCLUS] ),
        ALLEXCEPT ( Table15, Table15[Territoire], Table15[Site] )
    )
)

 

 

Best Regards,
Herbert

Message 10 of 10
2,135 Views
0
Phil_Seamark
Microsoft Employee
Microsoft Employee

‎04-14-2017 01:16 AM

Hi @pierredelareux

 

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. 🙂

 

 

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Message 2 of 10
2,265 Views
0
pierredelareux
Helper I
Helper I
In response to Phil_Seamark

‎04-14-2017 01:42 AM

Here

 

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   

 

Message 3 of 10
2,259 Views
0
Phil_Seamark
Microsoft Employee
Microsoft Employee
In response to pierredelareux

‎04-14-2017 01:46 AM

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 🙂

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Message 4 of 10
2,254 Views
0
pierredelareux
Helper I
Helper I
In response to Phil_Seamark

‎04-14-2017 02:21 AM

How can I upload an XLS file in this discussion ?

Message 5 of 10
2,250 Views
0
Phil_Seamark
Microsoft Employee
Microsoft Employee
In response to pierredelareux

‎04-14-2017 11:29 AM
Normally upload to Dropbox or One drive and share a link
To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Message 6 of 10
2,238 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All