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.
Hello everybody!
I need to calculate the number of articles that are 80 percent under a specific value.
I have an item master with the following data
Then I have a fact table with these data
Now I need to calculate the number of articles with a value of zero and the number of articles with under 80% of the corresponding target level.
There are some articles with no target level or a target level of zero. These articles shouldn't be added into this calculation.
How would you do that?
In the end I need a pivot table on the granularity of each article.
And a summary for all articles.
Calculated table (You can do this activity using EditQuery as well)
Table =
VAR _Actual =
CALCULATETABLE (
SUMMARIZECOLUMNS (
MasterTable[article],
"TargetValue", SUM ( MasterTable[target] ),
"ActualValue", CALCULATE (
SUM ( FactTable[value] ),
TREATAS (
VALUES ( MasterTable[article] ),
FactTable[article]
)
)
),
MasterTable[target] > 0
)
VAR _Percentage =
ADDCOLUMNS (
_Actual,
"Progress", [ActualValue] / [TargetValue]
)
RETURN
_Percentage
Dax measure for below 80%
below 80% =
VAR _Actual =
CALCULATETABLE (
SUMMARIZECOLUMNS (
MasterTable[article],
"TargetValue", SUM ( MasterTable[target] ),
"ActualValue", CALCULATE (
SUM ( FactTable[value] ),
TREATAS (
VALUES ( MasterTable[article] ),
FactTable[article]
)
)
),
MasterTable[target] > 0
)
VAR _Percentage =
ADDCOLUMNS (
_Actual,
"Progress", [ActualValue] / [TargetValue]
)
VAR _result =
COUNTROWS (
FILTER (
_Percentage,
[Progress] < .80
)
)
RETURN
_result
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Please do not create measures with SUMMARIZECOLUMNS in them. SUMMARIZECOLUMNS does not honor context transition.
Please read https://dax.guide/summarizecolumns/ for more information.
"SUMMARIZECOLUMNS does not support evaluation within a context transition. This makes it not useful in most of the measures – a measure with SUMMARIZECOLUMNS can be called also by CALCULATE but not in any case of context transition, including other SUMMARIZECOLUMNS statements. Client tools like Excel and Power BI almost always generate context transitions to evaluate measures in the reports."
Best
D
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |