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 | 500

2010 | CompB | UnitB | 200

2010 | CompB | UnitC | 300

2014 | CompA | UnitA | 600

...

 

So, for every company with different units you have Sales for different years.

 

I have a working measure that calculates the average sales (AVERAGEX) and depending on that I calculate three categories "Perfect" (>500), "OK (100-500)", "Bad (<100)" (SWITCH with using the AVERAGEX variable). 

I need a second measure or more so that I can show data like that:

Year | Category | Percentage

2010 | Perfect | 20 %

2010 | OK | 80 %

2014 | Perfect | 5 %

2014 | OK | 75 %

2014 | Bad | 20 %

...

 

My problem are the categories, because they are not in my data model but calculated in the measures. How can I "connect" the three values to the sales tables?

 

Best regards

 

Michael 

  • 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.

     

     

     

7 Replies

  • 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

  • talespin's avatar
    talespin
    Solution Sage
    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.

     

     

     

    • MichaelH78's avatar
      MichaelH78
      Frequent Visitor
      2 years ago

      ... oh, great, thanks talespin. That's what I was looking for. And the realisation is ultimately much easier than I thought 😉 Thank you.

  • 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