Forum Discussion

swathrddy's avatar
swathrddy
Icon for Helper I rankHelper I
2 years ago
Solved

Calculated Measures and charts

Hi,

Please find attached data for KPI dashboard:

 

I am struggling to calculate current month star rating

Ex: unable to pull star rating for one KPI where KPI no="5.1.1 Actual" , Date = current month (Date Table)

 

 

Any better way to data model?

 

Thank you.

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi rajendraongole1 ,Thanks for your quick reply, I will add more.

    Hi swathrddy ,

    Please follow these steps:

    1.Unpivot the selected columns

    2.Use the following DAX expression to create a table for slicer

    Table 2 = CALCULATETABLE(VALUES('Table'[KPI No]),'Table'[Indicators Description] <> "Target")

    3.Use the following DAX expression to create a column

    Date = EOMONTH([Attribute],0)

    4.Use the following DAX expression to create measures

    CurrentMonthKPI =
VAR _slicer =
    VALUES ( 'Table 2'[KPI No] )
VAR _table =
    FILTER (
        'Table',
        'Table'[KPI No]
            IN _slicer
                && DATE ( YEAR ( 'Table'[Attribute] ), MONTH ( 'Table'[Attribute] ), 1 )
                    = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
    )
RETURN
    CONCATENATEX ( _table, [Value], UNICHAR ( 10 ) )

    PreviousMonthKPI =
VAR _slicer =
    VALUES ( 'Table 2'[KPI No] )
VAR _table =
    FILTER ( 'Table', 'Table'[KPI No] IN _slicer )
RETURN
    CONCATENATEX ( _table, [Value], UNICHAR ( 10 ) )

    5.Final output

    Since there is no data for July in the table, it is displayed as 'Blank'. At the same time, I set a relative date for the 'Table' visual .

     

    Best Regards,
    Wenbin Zhou
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi swathrddy - Can you please share more information about with sample data/ pbix file, that helps to understand the problem

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

    • swathrddy's avatar
      swathrddy
      Icon for Helper I rankHelper I
      2 years ago

      Hi rajendraongole1, 

       

      Please find the data table.

      I am trying to create a KPI dashboard with gauge chart for current month scores and trend lines to show rolling 6 months. Data has different Targets, like Scale(1-5), Percentage and numbers with "Higher the better" or "Lower the better".

      Please let me know if you need me to share anything else.

      Regards!!

      OrderKPIKPI NoIndicators DescriptionTarget TypeTarget GuageOversightDepartmentNext assesment due dateReport FrequencyJan-24Feb-24Mar-24Apr-24May-24Jun-24
      Offer a range of options and identify opportunities that will improve person Centered care and choice PROVIDER OF CHOICE1.1.1 Acutal% of consumer feedback has been answered and responded to withing 5 working days Percentagehigher is betterAAAGovernance1/08/2024Monthly62%54%57%38%53%67%
      Offer a range of options and identify opportunities that will improve person Centered care and choice PROVIDER OF CHOICE1.1.1 TargetTargetPercentagehigher is betterAAAGovernance1/08/2024Monthly90%90%90%90%90%90%
      Board and Executive maintains an appropriate level of industry knowledge, diversity and skill to ensure quality governance.LEADERSHIP AND GOVERANCE2.1.1 ActualBoard membership monitored and gaps reported to Chair at a minimum of annually. Months since last reviewYes/Notarget metAAAGovernance MonthlyYesYesYesYesYesYes
      Board and Executive maintains an appropriate level of industry knowledge, diversity and skill to ensure quality governance.LEADERSHIP AND GOVERANCE2.1.1 TargetTargetYes/Notarget metAAAGovernance MonthlyYesYesYesYesYesYes
      Maintain a positive and values-based workforce and working environment.OUR PEOPLE3.1.1 ActualNumber of values nomination on GoogleNumberhigher is betterDDDGovernance Monthly3314156720
      Maintain a positive and values-based workforce and working environment.OUR PEOPLE3.1.1 TargetTargetNumberhigher is betterDDDGovernance Monthly202020202020
      Maintain a positive and values-based workforce and working environment.OUR PEOPLE3.1.3 ActualNumber of new workers comp claims in month Numberlower is betterAAAGovernance Monthly010010
      Maintain a positive and values-based workforce and working environment.OUR PEOPLE3.1.3 TargetTargetNumberlower is betterAAAGovernance Monthly000000
      Maintain a positive and values-based workforce and working environment.OUR PEOPLE3.1.4 ActualLTI ResidentialPercentagelower is betterAAAGovernance Monthly15%20%30%10%17%20%
      Maintain a positive and values-based workforce and working environment.OUR PEOPLE3.1.4 TargetTargetPercentagelower is betterAAAGovernance Monthly17%17%17%17%17%17%
      Ensure Company is a financially viable and sustainable organisation .SUSTAINABILITY4.1.1 ActualBudget is maintained within acceptable  variance Percentagehigher is betterCCCGovernance Monthly192%141%40%179%278% 
      Ensure Company is a financially viable and sustainable organisation .SUSTAINABILITY4.1.1 TargetTargetPercentagehigher is betterCCCGovernance Monthly10%10%10%10%10%10%
      Grow Company's services through partnerships,  alliances and identification that will result in an extension of servicesSUSTAINABILITY4.2.1 ActualEngagement by leadership team with external stakeholdersNumberhigher is betterLeadership TeamGovernance Monthly225264
      Grow Company's services through partnerships,  alliances and identification that will result in an extension of servicesSUSTAINABILITY4.2.1 TargetTargetNumberhigher is betterLeadership TeamGovernance Monthly222222
      RatingsSTAR RATING5.1.1 ActualStar Rating - Overall (Residential)Scale (1-5)higher is betterAAAGovernance Quarterly444444
      RatingsSTAR RATING5.1.1 TargetTargetScale (1-5)higher is betterAAAGovernance Quarterly333333

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi rajendraongole1 ,Thanks for your quick reply, I will add more.

    Hi swathrddy ,

    Please follow these steps:

    1.Unpivot the selected columns

    2.Use the following DAX expression to create a table for slicer

    Table 2 = CALCULATETABLE(VALUES('Table'[KPI No]),'Table'[Indicators Description] <> "Target")

    3.Use the following DAX expression to create a column

    Date = EOMONTH([Attribute],0)

    4.Use the following DAX expression to create measures

    CurrentMonthKPI =
VAR _slicer =
    VALUES ( 'Table 2'[KPI No] )
VAR _table =
    FILTER (
        'Table',
        'Table'[KPI No]
            IN _slicer
                && DATE ( YEAR ( 'Table'[Attribute] ), MONTH ( 'Table'[Attribute] ), 1 )
                    = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
    )
RETURN
    CONCATENATEX ( _table, [Value], UNICHAR ( 10 ) )

    PreviousMonthKPI =
VAR _slicer =
    VALUES ( 'Table 2'[KPI No] )
VAR _table =
    FILTER ( 'Table', 'Table'[KPI No] IN _slicer )
RETURN
    CONCATENATEX ( _table, [Value], UNICHAR ( 10 ) )

    5.Final output

    Since there is no data for July in the table, it is displayed as 'Blank'. At the same time, I set a relative date for the 'Table' visual .

     

    Best Regards,
    Wenbin Zhou
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.