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: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
1,471 Views
0
9 REPLIES 9
v-haibl-msft
Employee
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
1,419 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
1,393 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
1,356 Views
0
v-haibl-msft
Employee
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
1,330 Views
0
Phil_Seamark
Employee
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
1,460 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
1,454 Views
0
Phil_Seamark
Employee
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
1,449 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
1,445 Views
0
Phil_Seamark
Employee
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
1,433 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All