Forum Discussion

androo's avatar
androo
Advocate III
4 years ago
Solved

Pivot (Matrix) and Visual based on two Bins/Bucket columns

I am working in Power BI and am trying to solve a problem and create a visualisation that is pretty trivial in Excel, but, I can't figure out how to do it in PBi. I've uploaded an XL file which shows...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi androo ,

     

    Here I create a sample by data in Excel to have a test. 

    Here I inactive both relationships from [Bin Tag] to [Bin1] and [Bin2].

    Measure:

    Bin1 count = CALCULATE(DISTINCTCOUNT('Table'[ID]),USERELATIONSHIP('Table'[Bin1],Bin[Bin Tag]),'Table'[Bin1]<>BLANK())
    Bin2 count = CALCULATE(DISTINCTCOUNT('Table'[ID]),USERELATIONSHIP('Table'[Bin2],Bin[Bin Tag]),'Table'[Bin2]<>BLANK())
    Bin2 count based on Bin1 = CALCULATE([Bin1 count],'Table'[Bin2]<>BLANK())

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • androo's avatar
    androo
    4 years ago

    hi Rico,

    I thought the answer would be measures, and, thanks, yes it seems it is. A bit of a pain as it means I have to write two long measures creating the ten or so bins I need rather than using the PBi built in grouping directly, but, it works and means I can then use slicers from my category data with the visual and get sense. So, thanks. I've uploaded a version of my example file, in PBi fixed and with some superfluous elements removed so that the solution is as clear as possible.

androo's avatar
androo
Advocate III
4 years ago

I have been able to partially solve my problem. I created a BINs table in Power BI and then added two columns each counting the occurences of the Bin ID fromthe Bin Table value in the Bin 1 and then Bin 2 column of the data  table.

 

Curr Bin Count = calculate(DISTINCTCOUNT(EST077_Full_List[Property Reference]),filter(EST077_Full_List,EST077_Full_List[Bins: Curr PW]=Bins[Bin]))

 

Prop Bin Count = calculate(DISTINCTCOUNT(EST077_Full_List[Property Reference]),filter(EST077_Full_List,EST077_Full_List[Bins: Proposed PW]=Bins[Bin]))

 

For the limited aim of creating the "BIN table" and the "chart one" this has worked. However, it isn't a great result as I can't put this on another visual and then slice and fillter by other criteria in my model, which is why I think there is probably a better approach using measures.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi androo ,

 

Here I create a sample by data in Excel to have a test. 

Here I inactive both relationships from [Bin Tag] to [Bin1] and [Bin2].

Measure:

Bin1 count = CALCULATE(DISTINCTCOUNT('Table'[ID]),USERELATIONSHIP('Table'[Bin1],Bin[Bin Tag]),'Table'[Bin1]<>BLANK())
Bin2 count = CALCULATE(DISTINCTCOUNT('Table'[ID]),USERELATIONSHIP('Table'[Bin2],Bin[Bin Tag]),'Table'[Bin2]<>BLANK())
Bin2 count based on Bin1 = CALCULATE([Bin1 count],'Table'[Bin2]<>BLANK())

Result is as below.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.