Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Pivot graph in Power BI

Friends,

I am new in Power BI and wanted to create a similar figure I created in Excel Pivot very easily. I could not find exact same conditions in web so I need your help. My table is similar below

Location      Date      Time      Logic Text

A                 March    10        Above 4 days

B                   Feb       15          Below 1 Hr

C                   Jan       0.5         Below 1 Hr

B                   Jan       5              Normal

A                   Jan        0.6           Normal

 

I would like to plot a line graph showing the percent of total "Logic Text" count changes for each filtered Location and Date. Lets say I want to see my percent of total "Logic Text" "Above 4 days" from Jan to Mar for the location "A".

 

Percent of total should be calculated against locations, Date and Logic Text

 

Thanks in advance..

Ayaz

Need Help
Show and Tell
  • TeigeGao's avatar
    TeigeGao
    7 years ago

    Hi Anonymous ,

    In this scenario, we can create two measures, one for "Above 4 days", one for "Below 1 Hr". Please refer to the following two measures:

    Measure = var a = CALCULATE(SUM(Table1[WO Turn Around Time]))
var b = CALCULATE(SUM(Table1[WO Turn Around Time]),Table1[Logic Text] = "Below 1 Hr")
return DIVIDE(b,a,0)

Measure2 = var a = CALCULATE(SUM(Table1[WO Turn Around Time]))
    var b = CALCULATE(SUM(Table1[WO Turn Around Time]),Table1[Logic Text] = "Above 4 days")
    return DIVIDE(b,a,0)

    The result will like below:

    Best Regards,

    Teige

3 Replies

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    Hi Anonymous ,

    Could you please share the expected result like what's the X-asix like to us for analysis?

    Generally, we cna use the slicer visual to set the filter, then use selectedvalue to get the selected data.

    Best Regards,

    Teige

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

      Thanks for the feedback, much appreciated. What I want to see in my graph changes on percentage of "below 1 hr" and "above 4 hrs" counts out of total count for each location.

       

      Location can be put in filtering, not a problem. My problem was about the calculation of count percentages as a percentage of grand total for each individual "location" and "date". I dont want to see cumulitaive percentages but rather seperate for each month and put them into the graph to see the progress.

      This is the graph ultimately I would like to see for each location (which can be filtered, no problem);

       

       

       

      I attached the table I was working on but I gues I need to have some measures to do;

       

       

      Thanks in advance...

      • TeigeGao's avatar
        TeigeGao
        Solution Sage
        7 years ago

        Hi Anonymous ,

        In this scenario, we can create two measures, one for "Above 4 days", one for "Below 1 Hr". Please refer to the following two measures:

        Measure = var a = CALCULATE(SUM(Table1[WO Turn Around Time]))
var b = CALCULATE(SUM(Table1[WO Turn Around Time]),Table1[Logic Text] = "Below 1 Hr")
return DIVIDE(b,a,0)

Measure2 = var a = CALCULATE(SUM(Table1[WO Turn Around Time]))
        var b = CALCULATE(SUM(Table1[WO Turn Around Time]),Table1[Logic Text] = "Above 4 days")
        return DIVIDE(b,a,0)

        The result will like below:

        Best Regards,

        Teige