Calculate % of total by month with multiple filters from slicers

[CS99]

Hi,

Could you help create a measure which gives the correct % result when we select different slicer options from the dashboard. This should also consider the date range for denominator in the calculation.

Example dataset below:

Order Number	Order Date	Ordering Method	Doc Type
12345678	16/06/2024	Manual	ZA0
28462876	16/06/2024	EDI	ZA0
896736873	16/06/2024	EDI	ZA0
92823487234	17/06/2024	EDI	ZA0
864289462	18/06/2024	Manual	ZA1
7324782346	18/06/2024	EDI	ZA1
43275473482	17/07/2024	Manual	ZA0
3724562452	17/07/2024	EDI	ZA0
8736478323	17/07/2024	EDI	ZA0
26478236432	18/07/2024	Manual	ZA1
3678236428	18/07/2024	Manual	ZA1
2843672846	18/07/2024	Manual	ZA1
87264782643	19/07/2024	Manual	ZA0
236467229	19/07/2024	EDI	ZA0

Expected result :

	Order Count
Total Orders	14
Manual Orders %	7	50.0%
ZA1 %	3	21.4%
ZA1 Manual %	4	28.6%

[Anonymous · Accepted solution]

Thanks for the reply from @FreemanZ , please allow me to provide another insight: 
Hi  @CS99 ,

Here are the steps you can follow：

1. Create calculated table.

True1_Table =
var _table1=
SUMMARIZE('Table','Table'[Doc Type],'Table'[Ordering Method])
var _table2=
ADDCOLUMNS(
    _table1,
    "Column1",
    [Ordering Method]&" "&"Orders %",
    "Column2",
    [Doc Type]&" "&"%",
    "Column3",
    [Doc Type]&" "&[Ordering Method]&" "&"%",
    "Column4",
    "Total Orders")
return
_table2

True2_Table =
var _table1=
DISTINCT('True1_Table'[Column1])
var _table2=
DISTINCT('True1_Table'[Column2])
var _table3=
DISTINCT('True1_Table'[Column3])
var _table4=
DISTINCT('True1_Table'[Column4])
return
UNION(
   _table1, _table2,_table3,_table4)

2. Create measure.

Test1 =
var _selecttype=SELECTEDVALUE('Table'[Doc Type])
var _selectmethod=SELECTEDVALUE('Table'[Ordering Method])
return
SWITCH(
    TRUE(),
    CONTAINSSTRING(
    "Total Orders",MAX('True2_Table'[Ordering Method]))=TRUE(),
    COUNTX(ALL('Table'),'Table'[Order Number]),  
    CONTAINSSTRING(
    _selectmethod&" "&"Orders %",MAX('True2_Table'[Ordering Method]))=TRUE(),
    COUNTX(FILTER(ALL('Table'),'Table'[Ordering Method]=_selectmethod),[Order Number]),
    CONTAINSSTRING(
    _selecttype&" "&"%",MAX('True2_Table'[Ordering Method]))=TRUE(),
    COUNTX(FILTER(ALL('Table'),'Table'[Ordering Method]=_selectmethod&&'Table'[Doc Type]<>_selecttype),[Order Number]),
    CONTAINSSTRING(
    _selecttype&" "&_selectmethod&""&" %",MAX('True2_Table'[Ordering Method]))=TRUE(),
    COUNTX(FILTER(ALL('Table'),'Table'[Ordering Method]=_selectmethod&&'Table'[Doc Type]=_selecttype),[Order Number]),
     BLANK())

Test2 =
 DIVIDE(
    [Test1],COUNTX(ALL('Table'),'Table'[Order Number]))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[FreemanZ]

hi @CS99 ,

try to plot the visual with a measure like:

Measure = DIVIDE(COUNTROWS(data),COUNTROWS(ALL(data)))

it worked like: