Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
1 year ago
Solved

Wrong calculated column totals

Hi. I have this measure that calculates how much of their available holiday they have used and it works fine.

Leave_Booked % of Entitled = SUM('Holiday Data'[Leave Booked])/CALCULATE(SUM('Holiday Data'[Leave Entitlement]))
 
I tried then to create calculated column so I could use it as a filter for my tables.
Leave_Booked % of Entitled(column) = DIVIDE('Holiday Data'[Leave Booked],'Holiday Data'[Leave Entitlement])
And while the values are correct the total comes out too high.
on the left is measure and total for the measure is 87.9% where on the right calculated column total comes back as 24081.35%
I think this is because sum of the column, but is there fix that would give me correct column totals in the tables in case I would use it not just as a filter?

 

Need Help
  • pankajnamekar25's avatar
    pankajnamekar25
    1 year ago

    Hello Justas4478 

     

    try this approach

     

    Measure (correct total)

    Leave_Booked % of Entitled =

    DIVIDE(

        SUM('Holiday Data'[Leave Booked]),

        SUM('Holiday Data'[Leave Entitlement])

    )

     

     

    Calculated column (for filtering)

    Leave Usage Bucket =

    VAR Ratio = DIVIDE('Holiday Data'[Leave Booked], 'Holiday Data'[Leave Entitlement])

    RETURN

        SWITCH(

            TRUE(),

            Ratio <= 0.5, "Low (<=50%)",

            Ratio <= 0.75, "Medium (51%-75%)",

            Ratio <= 1, "High (76%-100%)",

            "Over 100%"

        )

    Thanks,
     Pankaj Namekar | LinkedIn

    If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

4 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    1 year ago

    Hello Justas4478 

     

    try this approach

     

    Measure (correct total)

    Leave_Booked % of Entitled =

    DIVIDE(

        SUM('Holiday Data'[Leave Booked]),

        SUM('Holiday Data'[Leave Entitlement])

    )

     

     

    Calculated column (for filtering)

    Leave Usage Bucket =

    VAR Ratio = DIVIDE('Holiday Data'[Leave Booked], 'Holiday Data'[Leave Entitlement])

    RETURN

        SWITCH(

            TRUE(),

            Ratio <= 0.5, "Low (<=50%)",

            Ratio <= 0.75, "Medium (51%-75%)",

            Ratio <= 1, "High (76%-100%)",

            "Over 100%"

        )

    Thanks,
     Pankaj Namekar | LinkedIn

    If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

    • Justas4478's avatar
      Justas4478
      Post Prodigy
      1 year ago

      pankajnamekar25 I tried with the sum and these are the results.
      All values then are showing column total value and not the individual value and the total is still wrong.

       
      The only way I found so far how to get correct column totals is if I SUM 'leave entitlement' and not 'leave booked'.

      DIVIDE('Holiday Data'[Leave Booked],SUM('Holiday Data'[Leave Entitlement]))

      But that gives too low values.

       

       

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    1 year ago

    Hi Justas4478 

    If you want  a slicer to filter out the percentage then you try this once.

    1. Create a table to use as a range slicer.
                

    Range = DATATABLE("range", STRING, {
        {"0-25%"}, {"25-50%"}, {"50-75%"}, {"75-100%"}, {"100%+"}
    })
     
    2.  Use your measures you created in below measures.

                     Show Leave Band =
    SWITCH(
    TRUE(),
    [Leave Used %] <= 0.25, "0-25%",
    [Leave Used %] <= 0.5, "25-50%",
    [Leave Used %] <= 0.75, "50-75%",
    [Leave Used %] <= 1, "75-100%",
    "100%+"
    )
     
     
     
    3.  Show Filtered Data =
    IF(MAX('LeaveBands'[Band]) = [Show Leave Band], 1, 0)
     
    4. In the visual level filter set 1 for Show Filtered Data
     
     

     If this answers your questions, kindly accept it as a solution and give kudos.

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Justas4478 

    Measures don't have a row context so they can't be used in a slicer as only dimensions can be. What you can is to evaluate that measure against a physical column and use that column in the slicer instead.

    Please see the attached sample pbix.