Forum Discussion

rautaniket0077's avatar
rautaniket0077
Icon for Resolver I rankResolver I
1 year ago

Aggregation based on group by/ summarization and filter.

 Hi, i have attached the sample data now there are couple of things that i want to achieve using power bi-   1) first thing is i need to filter the table  based on the month-year selected from the ...
rautaniket0077's avatar
rautaniket0077
Icon for Resolver I rankResolver I
1 year ago

Hi@v-jiewu-msft, tharunkumarRTK, DataNinja777,@mark_endicott , johnt75 , DataNinja777, amitchandak, lbendlin,

The measure which tharunkumarRTK given is working fine with the sample data but its giving me blank values in my current report instead i have used the below measures to calculate internal, external, warranty and insurance.

internal new =

var _date = MAX(Calender[Date])

var _result =
CALCULATE(
    SUM(Fact_Car_Service_Summary[internal]),
    DATESINPERIOD(
        Calender[Date],
        EOMONTH(_date,-1),
        -12,
        MONTH)
)

return _result

same way i have calculated for the rest.

now the thing is i just need the count whenever there is a sum of internal >0 and rest i.e. insurance, warranty and external is 0. I have uesed the below measure in which i have used IF but i know that this is not giving  me the count instead it is compairing and giving 1 and 0.

Internal cnt new corrected =

var _date = MAX(Calender[Date])

var _result =
CALCULATE(
    DISTINCTCOUNT(Fact_Car_Service_Summary[vin]),
    DATESINPERIOD(
        Calender[Date],
        EOMONTH(_date,-1),
        -12,
        MONTH
    )
)

RETURN
IF(
    [external new]+
    [warranty new]+
    [insurance new]=0,
    _result,
    0
)
Note :- i need to show the count in separate KPI card and not in table visual.

Thank you.




 






  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,

    Try creating a new page and re-adding the table data, metrics, and filters on that page to see if there are still issues. The expression provided above gives the correct results.

     

    Best Regards,

    Wisdom Wu

    • PawarNovil's avatar
      PawarNovil
      Icon for Resolver I rankResolver I
      1 year ago

      Hello Aniket,

      Based on the data and discussions in the posts, I have created a PBIX file that includes two solutions: one with a relationship and one without. Please take a look at the attached PBIX for further details.

      attachment: https://drive.google.com/file/d/1gFeD3iqrO5eLYCfCuzsxfFH9ibRS2tkg/view?usp=sharing

      ------------------------------------------------------------------------------------------------

      Regards,

      Novil

      If I answer your question, please mark my post as a solution.