Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost 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 ta...
Need Help
nandukrishnavs's avatar
nandukrishnavs
Icon for Community Champion rankCommunity 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