Forum Discussion

adm2026's avatar
adm2026
Regular Visitor
1 year ago
Solved

Working with formula and ratios

Hi All, 


Relitivley new to Bi - apologies if I don't ask get the right terminology first time. 

I'm working with a power Bi report connected to 4 sharepoint lists (these lists are also connected to a PowerApp)

 

The app I built is effectivly an asset management tool. I've placed a table on a card in Bi where I can display different columns within the table from the different data sources, but what I'm stuggling with is to show Kit Ratio to Learner for example 1:6 or 1:12
My table shows Asset Category | Count of ID (which shows how many are in stock) 

I did some digging and played around with copilot, but I kept running into errors because some of the data in the dataset was saved as strings instead of actual values. At the end of the day, I would to add a column at the end of that table to show whether I have the right number of assets or if I'm over or under in each category.

 

 

Need Help
Tips and Tricks
  • adm2026's avatar
    adm2026
    1 year ago

    Hi - Thanks for your response and apologies for my delayed response over the Christmas period. 
    I just want to point out, I have not had the opportunity to try this possible solution - neither did I accept this as a working solution for my question.

    I managed to achieve my goal with a measure and the following formula:

    DifferenceValue = 
    COUNT(PowerAssets[Id]) - 
    INT(
        CEILING(
            DIVIDE(
                SUM('PowerAssetsTCtrs'[Totals]),
                SUM('PowerAssetsCategories'[RatioLearner])
            ),
            1
        ) * SUM('PowerAssetsCategories'[RatioKit])
    )

    Then I added some conditional formatting for the Icons

    For the assets per Centre and Classroom, I used the following:

    DifferenceValue Per Centre = 
VAR AssetCount = COUNT(PowerAssets[Id])
VAR PerCentreValue = CALCULATE(SUM(PowerAssetsCategories[PerCentre]))
VAR Result = AssetCount - PerCentreValue
RETURN IF(Result = 0, 0, Result)




7 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    What would be your expected result based on the sample data you are showing?

  • adm2026's avatar
    adm2026
    Regular Visitor
    1 year ago

    I’m aiming to accomplish something like this

    Basically telling me that I have overstock that needs to be re-distributed, or not enough stock.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi adm2026 ,

     

    What is the calculation logic for the "Stock" column? Also, you say that you show columns from different tables in the visual, so you should have created relationships in those tables? Can you show me relationship diagram? If you used measures or calculated columns in this screenshot, please include them.

     

     

    Best regards,

    Mengmeng Li

    • adm2026's avatar
      adm2026
      Regular Visitor
      1 year ago

      Hi Anonymous 
      The excel table I referenced was just an example made in excel of what I was trying to achieve - there are no formulas (just manual calculations)

      For example the first item, 28 (Learners) / 6 (RatioLearners) = 4.66 (rounded down to 4)
      Two bones for every 6 learners with 12 in stock = 8 sets of bones needed with 2 remaining or 1 remaining if I rounded 4.66 to 5 instead, which thinking about it would be better.

       

      I have created relationships between the tables

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi adm2026 ,

     

    Based on the calculation you provided, there is clearly an error in your expected output. If you rounded down to 4, then the Stock of both the first record and the second record should be 4.If you rounded up to 5, the first inventory should be 2 and the second should be 3. I'm not quite sure if there's any other calculation logic here or if it's just a calculation error.

     

    If it's just a calculation error, you can create such a measure(Round up to 5) in your report using the calculation method you provided.

    Stock = VAR learner = 28
RETURN 
MAX('Table'[Asset Count])-(ROUNDUP(learner/MAX('Table'[RatioLearner]),0)*MAX('Table'[RatioKit]))

     

     

     

    Best regards,

    Mengmeng Li

    • adm2026's avatar
      adm2026
      Regular Visitor
      1 year ago

      Hi - Thanks for your response and apologies for my delayed response over the Christmas period. 
      I just want to point out, I have not had the opportunity to try this possible solution - neither did I accept this as a working solution for my question.

      I managed to achieve my goal with a measure and the following formula:

      DifferenceValue = 
    COUNT(PowerAssets[Id]) - 
    INT(
        CEILING(
            DIVIDE(
                SUM('PowerAssetsTCtrs'[Totals]),
                SUM('PowerAssetsCategories'[RatioLearner])
            ),
            1
        ) * SUM('PowerAssetsCategories'[RatioKit])
    )

      Then I added some conditional formatting for the Icons

      For the assets per Centre and Classroom, I used the following:

      DifferenceValue Per Centre = 
VAR AssetCount = COUNT(PowerAssets[Id])
VAR PerCentreValue = CALCULATE(SUM(PowerAssetsCategories[PerCentre]))
VAR Result = AssetCount - PerCentreValue
RETURN IF(Result = 0, 0, Result)




      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi adm2026 ,

         

        Thanks for your response!!

        You can accept your reply as solution to let it be seen by more people in need.

         

        Best regards,

        Mengmeng Li