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
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.

    • kennylch's avatar
      kennylch
      Helper I
      2 years ago

      i tried with the caclulated but doesnt work. as the calculated column cant the take in the parameter slicer i have on top. That's dynamic and user may change the parameter when using. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi kennylch ,

         

        Pie charts can accept multiple MEASURES as values. In your case, there are only three categories, and you can create measures for them individually like this:

        Healthy Count = 
VAR __table = SUMMARIZE('KFACTOR','SAMPLE'[AC_CODE],'SAMPLE'[PROGRAM_CODE],'SAMPLE'[POPULATION],'KFACTOR'[PART],KFACTOR[ORDER_QTY],'KFACTOR'[Net Qty],"@Measure",[MEASURE])
RETURN
    COUNTROWS( FILTER(__table,[@Measure]="Healthy" ))
        Above Healthy Count = 
VAR __table = SUMMARIZE('KFACTOR','SAMPLE'[AC_CODE],'SAMPLE'[PROGRAM_CODE],'SAMPLE'[POPULATION],'KFACTOR'[PART],KFACTOR[ORDER_QTY],'KFACTOR'[Net Qty],"@Measure",[MEASURE])
RETURN
    COUNTROWS( FILTER(__table,[@Measure]="Above Healthy" ))
        Unhealthy Count = 
VAR __table = SUMMARIZE('KFACTOR','SAMPLE'[AC_CODE],'SAMPLE'[PROGRAM_CODE],'SAMPLE'[POPULATION],'KFACTOR'[PART],KFACTOR[ORDER_QTY],'KFACTOR'[Net Qty],"@Measure",[MEASURE])
RETURN
    COUNTROWS( FILTER(__table,[@Measure]="Unhealthy" ))

        Then rename the field for the visual:

        For the original [Measure] you can refer to the solution given by Shahariar or aduguid.🤗

         

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum