percentage of grand total with condition

[Paulompm]

Hello

I have a question about dax formulas. My data is distributed as follows:

product line	Subgroup	$
line 1	x	10
line 1	a	200
line 1	y	100
line 2	z	200
line 2	w	1000
line 3	m	400
line 3	f	500
line 3	p	500
line 4	o	300
line 4	k	100
line 4	r	800

The column product line can be repeated. The subgroup column does not repeat itself.

My goal is to find the percentage of the subgroup just for the same product line. Example:

product line	Subgroup	$	%Grand Total by subgroup
line 1	x	10	3,23%
line 1	a	200	64,52%
line 1	y	100	32,26%
line 2	z	200	16,67%
line 2	w	1000	83,33%
line 3	m	400	28,57%
line 3	f	500	35,71%
line 3	p	500	35,71%
line 4	o	300	25,00%
line 4	k	100	8,33%
line 4	r	800	66,67%
TOTAL		4110	400,00%

If you look at the total percentage is 400% because each product line is individualized by subgroup.

 I would also like to know if there is a way to elect the subgroups that make 80% of my revenue(pareto analysis).

Thanks for the attention.

[Vvelarde]

@Paulompm

Hi, you can obtain using this DAX

% of SubGroup in Line =
IF (
    HASONEVALUE ( Table1[product line] ),
    DIVIDE (
        SUM ( Table1[$] ),
        CALCULATE ( SUM ( Table1[$] ), ALLEXCEPT ( Table1, Table1[product line] ) )
    ),
    1 * DISTINCTCOUNT ( Table1[product line] )
)

 

[Paulompm]

Thanks for your response. The formula works perfectly. But I would like to ask a new question. I have created a relation of this table with a classic calendar. When filter months and years the percentage does not work properly. Do I have to change anything?

[Vvelarde]

@Paulompm

Show me what is the result and the expected result

[Paulompm]

@Vvelarde here is the sample again:

Date	product line	Subgroup	$
02/05/2017	line 1	x	10
10/05/2017	line 1	a	200
05/06/2016	line 1	y	100
04/05/2017	line 2	z	200
07/05/2017	line 2	w	1000
08/06/2016	line 3	m	400
03/05/2017	line 3	f	500
12/05/2017	line 3	p	500
13/06/2016	line 4	o	300
05/06/2016	line 4	k	100
05/05/2017	line 4	r	800

Result with the filter:

expected result:

[Vvelarde · Accepted solution]

@Paulompm

Hi, made a little adjustment to Code and Try it (in Bold)

% of SubGroup in LineA =
IF (
    HASONEVALUE ( Table3[product line] ),
    DIVIDE (
        SUM ( Table3[$] ),
        CALCULATE (
            SUM ( Table3[$] ),
            ALLEXCEPT ( 'Table3', Table3[product line] ),
            VALUES ( 'Calendar'[Date] )
        )
    ),
    1 * DISTINCTCOUNT ( Table3[product line] )
)

[Paulompm]

Thank you. Worked perfectly!