Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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% |
Solved! Go to 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
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
hi @CS99 ,
try to plot the visual with a measure like:
Measure = DIVIDE(COUNTROWS(data),COUNTROWS(ALL(data)))
it worked like:
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
10 | |
6 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |