Countrows with filter

[joshua1990]

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

• article
• target level

Then I have a fact table with these data

• article
• value
• date

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.

[nandukrishnavs]

@joshua1990 

FactTableMasterTable

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 🙂

[Anonymous]

@nandukrishnavs

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