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.

     

     

     

Rabi's avatar
Rabi
Resolver I
2 years ago

Hi Mate

 

 

If this is what you meant follow the steps below:

1) Add a new column sales%

Sales% = 
var A=
CALCULATE(sum(Sales[Sales]),ALLSELECTED(Sales[Company]))
VAR B=SUM(Sales[Sales])
RETURN
A/B

2) Add Category Column

Category = 
if(Sales[Sales]>500,"Perfect",if(Sales[Sales]>=100 && Sales[Sales]<=500,"OK",IF(Sales[Sales]<100,"BAD")))

3) Calculate average sales with the measure

Average Sales = average(Sales[Sales])

 

Visualise the data Now

Hope it helped, Mark it correct if so !!

 

Rabi

Linkedin

 

  • MichaelH78's avatar
    MichaelH78
    Frequent Visitor
    2 years ago

    Hello Rabi,

     

    Thank you for your help. Unfortunately, this does not lead to success, which is mainly due to the fact that Sales% does not work as a calculated column, because the percentage value changes depending on the filter context. For example, if I filter on UnitB and UnitC, the result is different from UnitA, UnitB and UnitC.

    My current status is as follows:

    • I have created a table categories which has a column Cat containing the three values "Perfect", "OK" and "Bad".
    • I have created a measure CatPerc as follows:
    CatPerc = 
    VAR vAVG = AVERAGEX(Sales, Sales[Sales])
    VAR vCat =
    SWITCH(
    TRUE(),
    vAVG <= 100, "Bad",
    vAVG > 100 && vAVG <= 500, "OK",
    vAVG > 500, "Perfect"
    )
    VAR vTabFilterNoComp =
    SUMMARIZE(
    CALCULATETABLE( Sales, REMOVEFILTERS( Sales[Company] ) ),
    Sales[Year],
    Sales[Company],
    "Cat", vCat
    )
    VAR vTabCatGroups =
    SUMMARIZE(
    vTabFilterNoComp ,
    Sales[Year],
    [Cat],
    Sales[Company],
    "Cnt", 1
    )
    VAR vCountAll = SUMX( vTabCatGroups , [Cnt] )
    VAR vTabCat =
    CROSSJOIN( VALUES( Years[Year] ), VALUES( Categories[Cat] ) )
    --VAR vCountKat = SUMX( vTabCat , Categories[Cat] )
    RETURN vCountAll
    • I have added a table visual in which I have inserted Sales[Year], Categories[Cat] and the measure CatPerc. Now I have at least the total number of averages awarded across all companies for each year and category. But now I still need the number of assigned category values across the companies in order to determine the percentage.

    This is what I want:

    But at the moment I only have the total values per year (i.e. 5 for 2010, 6 for 2014 and 3 for 2018).

     

    I think my main problem is that the categories have no real relationship to the sales, right? I hope my problem is described reasonably comprehensibly 😉

     

    Thanks in advance, Michael