Forum Discussion

kennylch's avatar
kennylch
Helper I
2 years ago
Solved

duplicated row in matrix

I have this table after i added 'measure' in the table. It cause all the part number repeating. 

 

Not sure why is it so

Test Data modified.pbix 

 

 

 

Need Help
  • shafiz_p's avatar
    shafiz_p
    2 years ago

    Hi kennylch  You not mentioned what will happen when there is blank in your measure formula. You just programmed all the others will be unhealthy excep greater than 0 and 5. In case you want to use your measure instead solution provided by aduguid , you can modify your measure as provided below:

    Measure = SWITCH(
    True(),
    (sum(KFACTOR[Net Qty])- (sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]))/(sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]) < 0, "Unhealthy",
    (sum(KFACTOR[Net Qty])- (sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]))/(sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value])>5,"Above Healthy",
    (sum(KFACTOR[Net Qty])- (sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]))/(sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value])>0,"Healthy"
    )
     
    OR
     
    Measure = SWITCH(
    True(),
    (sum(KFACTOR[Net Qty])- (sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]))/(sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value])>5,"Above Healthy",
    (sum(KFACTOR[Net Qty])- (sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]))/(sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value])>0,"Healthy",
    (sum(KFACTOR[Net Qty])- (sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]))/(sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]) = BLANK(), BLANK(),
    "Unhealthy"
    )

    Hope this helps!!
    If this solved your problem, please accept it as a solution!!

    Best Regards,
    Shahariar Hafiz

6 Replies

  • aduguid's avatar
    aduguid
    Memorable Member
    2 years ago

    Try this for the measure

    HealthStatus =
IF(
    ISBLANK([Healthy]),
    BLANK(),
    IF(
        [Healthy] > 0,
        "Healthy",
        "Unhealthy"
    )
)
  • shafiz_p's avatar
    shafiz_p
    Super User
    2 years ago

    Hi kennylch  You not mentioned what will happen when there is blank in your measure formula. You just programmed all the others will be unhealthy excep greater than 0 and 5. In case you want to use your measure instead solution provided by aduguid , you can modify your measure as provided below:

    Measure = SWITCH(
    True(),
    (sum(KFACTOR[Net Qty])- (sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]))/(sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]) < 0, "Unhealthy",
    (sum(KFACTOR[Net Qty])- (sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]))/(sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value])>5,"Above Healthy",
    (sum(KFACTOR[Net Qty])- (sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]))/(sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value])>0,"Healthy"
    )
     
    OR
     
    Measure = SWITCH(
    True(),
    (sum(KFACTOR[Net Qty])- (sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]))/(sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value])>5,"Above Healthy",
    (sum(KFACTOR[Net Qty])- (sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]))/(sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value])>0,"Healthy",
    (sum(KFACTOR[Net Qty])- (sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]))/(sum(KFACTOR[KFACTOR])*'Launch Plan'[Launch Plan Value]) = BLANK(), BLANK(),
    "Unhealthy"
    )

    Hope this helps!!
    If this solved your problem, please accept it as a solution!!

    Best Regards,
    Shahariar Hafiz
    • kennylch's avatar
      kennylch
      Helper I
      2 years ago

      That's correct. Anyway, I tried to create a pie chart using the measure result. but i am not able to do so. 

       

      E.g. a pie chart with 3 cluster (Above healthy, Healthy level, Unhealthy" But not able to add this measure as legend

       

      • shafiz_p's avatar
        shafiz_p
        Super User
        2 years ago

        You would not be able to use this as a legend. Each category depends on calculation. You just can't use like this. If you want to use such category, then you need to create calculated column.

        Anyway aduguid  and me tried to provide solution, what you showed us.