Forum Discussion

MichaelH78's avatar
MichaelH78
Frequent Visitor
2 years ago
Solved

Count data with calculated values

Hi,   I'm new here and I hope you can help me.    I have a sales table like that: Year | Company | Unit | Sales 2010 | CompA | UnitA | 100 2010 | CompA | UnitB | 200 2010 | CompB | UnitA | 50...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi MichaelH78 ,

     

    Hope all is going well.

     

    Please follow these steps:

     

    1.Create Category calculated columns based on different conditions.

    Category = IF((Sales[Sales]) >= 500,"perfact",IF((Sales[Sales]) < 100,"bad","ok"))

     

    2.Count rows based on different years.

    AllRowsByYear = CALCULATE(COUNTROWS('Sales'), ALLEXCEPT('Sales', 'Sales'[Year]))

     

    3.Create a table.

    Table = SUMMARIZE (
    Sales,
    Sales[Year],
    Sales[Category],
    "RowCount", COUNTROWS(Sales),
    "Percentage", DIVIDE (
        COUNTROWS ( FILTER ( Sales, Sales[Year] = Sales[Year] && IF((Sales[Sales]) >= 500,"perfact",IF((Sales[Sales]) < 100,"bad","ok")) = Sales[Category] ) ),
        [AllRowsByYear]
    )
)

     

    4.Drag the fields you need from the new table to the report page for display.

     

    pbix file is attached.

     

    Your needs should be solved at this point. If you have any questions, please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

  • talespin's avatar
    talespin
    2 years ago

    hi MichaelH78 ,

     

    Please test it thoroughly. I am taking average of sales and then creating category. Is that the logic?

     

    Step1 Create a calculated table, do not link it to any other table. This is just to hold all combination of Year and Category. Place Year and Category in table visual along with the measure.

     

    YearSegment = CROSSJOIN( SUMMARIZE( TestTbl5, TestTbl5[Year]) , {"Bad","OK","Perfect"})
     
    Step2
    Create a measure 
    MSales =
    VAR _Year = SELECTEDVALUE( YearSegment[Year])
    VAR _Segment = SELECTEDVALUE(YearSegment[Value])
    VAR _SUMMTbl =
    ADDCOLUMNS(
            SUMMARIZE( TestTbl5, TestTbl5[Year], TestTbl5[Company]),
            "Category",            
            VAR _AVGSales = CALCULATE( AVERAGE( TestTbl5[Sales]) )
            RETURN IF( _AVGSales < 100, "Bad",
                        IF( _AVGSales >= 100 && _AVGSales <= 500, "OK",
                            IF( _AVGSales > 500, "Perfect", BLANK()
                            )
                        )
            )
    )

    VAR _TotalCount = COUNTX(
                                FILTER(_SUMMTbl, [Year] = _Year), 1
    )

    VAR _CategoryCount = COUNTX(
                                FILTER(_SUMMTbl, [Year] = _Year && [Category] = _Segment), 1
    )

    RETURN DIVIDE( _CategoryCount, _TotalCount)

     

     

    You can see all the mockup data on the right side to validate the logic and results. PLease test with yours.

     

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi MichaelH78 ,

 

Hope all is going well.

 

Please follow these steps:

 

1.Create Category calculated columns based on different conditions.

Category = IF((Sales[Sales]) >= 500,"perfact",IF((Sales[Sales]) < 100,"bad","ok"))

 

2.Count rows based on different years.

AllRowsByYear = CALCULATE(COUNTROWS('Sales'), ALLEXCEPT('Sales', 'Sales'[Year]))

 

3.Create a table.

Table = SUMMARIZE (
    Sales,
    Sales[Year],
    Sales[Category],
    "RowCount", COUNTROWS(Sales),
    "Percentage", DIVIDE (
        COUNTROWS ( FILTER ( Sales, Sales[Year] = Sales[Year] && IF((Sales[Sales]) >= 500,"perfact",IF((Sales[Sales]) < 100,"bad","ok")) = Sales[Category] ) ),
        [AllRowsByYear]
    )
)

 

4.Drag the fields you need from the new table to the report page for display.

 

pbix file is attached.

 

Your needs should be solved at this point. If you have any questions, please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

MichaelH78's avatar
MichaelH78
Frequent Visitor
2 years ago

Hello Yang, thank you very much for your detailed help.

 

Unfortunately, I accepted your solution too early, because it does not work correctly yet 😉 When I insert a slicer, on Sales[Unit], nothing changes, which would be necessary. I still believe that the calculated column does not help, because the values should change depending on the selection of Unit.

 

I have inserted my current status in your Demo.pbix. The measure MyPercentage reacts correctly to the slicer and shows me the total number of category values (grouped by year, company and unit) for each year. Now I "only" need the number per different category value (Perfect, OK, Bad).

 

Here is my PBIX.

 

Best regards

 

Michael