Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
6 years ago

Countrows with filter

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.

Need Help

2 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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