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.
Dear members,
i've got some headaches with calculation which is looks easy to calculate by eyes and impossible for me to get correct result in PBI.
Goal: to get correct claims statistics and their visualization
Sample data are located here https://dropmefiles.com/AdxlX
Data description:
Rules for KPI calculation:
Obstacles/headaches:
using:
Nr of confirmed orders = CALCULATE(DISTINCTCOUNT('Sheet1'[ORDER number]);FILTER('Sheet1';'Sheet1'[ORDER number]>0&&'Sheet1'[Claim is taken for KPI calculation or not]="taken"&&'Sheet1'[Investigation result]="confirmed"))
Nr of NOT confirmed orders = CALCULATE(DISTINCTCOUNT('Sheet1'[ORDER number]);FILTER('Sheet1';'Sheet1'[ORDER number]>0&&'Sheet1'[Claim is taken for KPI calculation or not]="taken"&&'Sheet1'[Investigation result]="not confirmed"))
i have got oucome that the same order is calculated as confirmed and not confirmed due to some of its SKUs are confirmed and not confirmed (as an example above, order 53901 )
is it possible somehow to calculate correct results for 2 questions?
thank you in advance.
Solved! Go to Solution.
Hi @Anonymous ,
Do you mean to show "Prompt page" based on which measure you select? Tooltip will show values based on the context of current row, so I think it is impossible to do it in one table. You could create two tables with two measures in tooltip page.
Yes =
CONCATENATEX (
CALCULATETABLE (
DISTINCT ( Sheet1[ORDER number] ),
FILTER ( Sheet1, 'Sheet1'[Investigation result] = "confirmed" )
),
'Sheet1'[ORDER number],
", "
)
Not =
CONCATENATEX (
CALCULATETABLE (
DISTINCT ( Sheet1[ORDER number] ),
FILTER ( Sheet1, 'Sheet1'[Investigation result] = "not confirmed" )
),
'Sheet1'[ORDER number],
", "
)
Here is the result.
Hi @Anonymous ,
Do you mean to show "Prompt page" based on which measure you select? Tooltip will show values based on the context of current row, so I think it is impossible to do it in one table. You could create two tables with two measures in tooltip page.
Yes =
CONCATENATEX (
CALCULATETABLE (
DISTINCT ( Sheet1[ORDER number] ),
FILTER ( Sheet1, 'Sheet1'[Investigation result] = "confirmed" )
),
'Sheet1'[ORDER number],
", "
)
Not =
CONCATENATEX (
CALCULATETABLE (
DISTINCT ( Sheet1[ORDER number] ),
FILTER ( Sheet1, 'Sheet1'[Investigation result] = "not confirmed" )
),
'Sheet1'[ORDER number],
", "
)
Here is the result.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |