Forum Discussion

Akkermans's avatar
Akkermans
New Member
3 years ago

Transforming a measures in count functions

Sorry for the lengthy post, I have been stuck on this for a while and I am relatively new to PowerBI. Thanks in advance for the help! 

 

Background:

I've made an overview of techniques my employees are trained for and I have been able to develop a few measures to calculate the variation trained vs target, which I displayed in a bar chart. I have a few instances where the amount of people trained for a technique does not match the target itself, of which I would like a count (for the purpose: how many techniques should I focus on).

One of the challanges is the excel sheet contains previous dates with the same techniques with varying % trained at the time of recording, which I would like to keep so I can assess progression over time. 

 

Example:

Date:        technique       trained(%)      target(%)

1 jan 22      diluting          10                    20

1 Jan 22      weighing        50                   40

10 feb 22    diluting          15                   20

10 feb 22    weighing         50                    50

 

Question: 

How do I make a count of the number of unmet trainings and only use the latest data available (which can change if I filter the sheet to a different date)? 

 

 

Current used measures:

Met training =
VAR trained = [Trained per technique]
VAR target = [Target per technique]
RETURN
IF(
    trained>=target,
    [Marker training] - [Bar training]
)
------------------------
Target per technique =
CALCULATE(
    MAX([Target (%)]),
    KEEPFILTERS(VALUES(training_data[Techniques])))
---------------------------------
Trained per technique =
CALCULATE(
    MAX([Trained (%)]),
    KEEPFILTERS(VALUES(training_data[Techniques])))
-------------------------------------
Unmet training =
VAR trained = [Trained per technique]
VAR target = [Target per technique]
RETURN
IF(
    trained<target,
    [Marker training]-[Bar training]
)
-------------------------------------
Bar training =
MIN([Target per technique],
[Trained per technique]
)
------------------------------------------
Marker training =
MAX(
    [Target per technique],
    [Trained per technique]
)
--------------------------------------------

 

Need Help

3 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Akkermans ,

     

    Would you be able to provide a screenshot of the desired result so that I can answer it for you as soon as possible.


    Looking forward to your reply.


    Best Regards,
    Henry

     

    • Akkermans's avatar
      Akkermans
      New Member
      3 years ago

      Dear v-henryk-mstf , 

       

      Thanks for your replay (sorry I posted mine incorrectly), what i want is ratehr simple I want a chard visual which displays the number of unmet trainings. If I make a card of the messure at the moment is displays it in % and their is no options to change it to count. 

       

      Kind regards, 

       

      Akkermans

       

  • Akkermans's avatar
    Akkermans
    New Member
    3 years ago

    Dear v-henryk-mstf , 

     

    Thanks for your reply, what I want is rather simple I want a card visual which displays the number of unmet trainigs. If I make a card of the messure at the moment is displays it in %. 

     

     

    kind regards, 

     

    Akkermans