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
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Create a calendar table

    calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

    Create a relationship for two tables

     

    Create columns/measures in your main data table

    create a column

    year/month = YEAR(Sheet1[date])&"/"&MONTH(Sheet1[date])

    create measures

    ss value = CALCULATE(SUM(Sheet1[value]),FILTER(ALLSELECTED(Sheet1),Sheet1[year/month]=MAX(Sheet1[year/month])&&Sheet1[text]="ss"))

ww value = CALCULATE(SUM(Sheet1[value]),FILTER(ALLSELECTED(Sheet1),Sheet1[year/month]=MAX(Sheet1[year/month])&&Sheet1[text]="ww"))

all value = CALCULATE(SUM(Sheet1[value]),FILTER(ALLSELECTED(Sheet1),Sheet1[year/month]=MAX(Sheet1[year/month])))

ss/all = [ss value]/[all value]

ww/all = [ww value]/[all value]

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    Which do you want to show on X-axis and Y-axis of the line chart?

    Does my visual satisfy your needs?

     

     

    In addition, "my percent of total "Logic Text" "Above 4 days" from Jan to Mar for the location "A" ", 

    how to calculate the percent? could you show me a calculation?

     

    Best Regards
    Maggie

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Maggie,

      Thanks for the reply.

       

      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);

       

      The table that I was working on;

       

      Thanks in advance...

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi Anonymous 

        Create a calendar table

        calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

        Create a relationship for two tables

         

        Create columns/measures in your main data table

        create a column

        year/month = YEAR(Sheet1[date])&"/"&MONTH(Sheet1[date])

        create measures

        ss value = CALCULATE(SUM(Sheet1[value]),FILTER(ALLSELECTED(Sheet1),Sheet1[year/month]=MAX(Sheet1[year/month])&&Sheet1[text]="ss"))

ww value = CALCULATE(SUM(Sheet1[value]),FILTER(ALLSELECTED(Sheet1),Sheet1[year/month]=MAX(Sheet1[year/month])&&Sheet1[text]="ww"))

all value = CALCULATE(SUM(Sheet1[value]),FILTER(ALLSELECTED(Sheet1),Sheet1[year/month]=MAX(Sheet1[year/month])))

ss/all = [ss value]/[all value]

ww/all = [ww value]/[all value]

         

        Best Regards
        Maggie

         

        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.